I’m in the process of weeding my nonfiction. As I’m sure any librarian well tell you weeding is a chore, but it has to be done. In grad school we used Karen Lowe’s Resource Alignment text (Resource Alignment: Curriculum Support in the School Library Media Center (c2020, 2016, by Karen R. Lowe, email@example.com)) to go through a sample set of text to grade the books (keep, useful and discard) based off their Dewey number and then the year the book was published. The book has a nice big multi-page chart for this, and you can look up every book by it’s Dewey number and then look at the year to help you make your decision if you will keep the book or discard it. I have over 6000 books in my nonfiction and many libraries probably have more. That is a lot of time to go through and manually check the books. To automate this you will need 3 things:
- Lowe’s Resource Alignment data in an Excel form.
- Your books exported from your catalog. You will need to make sure that the export has both the Dewey # and the Publication Year for the books.
- The VBA Script (further down in this document).
Resource Alignment Data
The book I bought came with the list of books in a word document to which I converted to an Excel document. Below is a sample image of what my converted table looks like. I have started a guide on how to convert your Word document of the Resource Alignment Data into a usable Excel Document for this script to use. You can read the guide here.
You will need to make sure that you name your Excel Sheet that has the information from the Resource Alignment, Compare or change it in the script below. It can be called anything, they just have to be the same for the script to recognize where to look.
Export your collection, just make sure that what you are exporting can be read into excel and that it has the Dewey number, the publication year, and anything else you might decide is necessary – such as the book title. Here is a screen shot of the first few rows of my collection. Row A is the Dewey #. I used the split at first space equation
to separate the Dewey number. I have more about this in section 4 of the converting the resource alignment table over. In my document below Column A is the Dewey and H is the publication year. It is important to note where these columns are for the script to work properly. I have written comments in the code so you should know where they need to go. These are the only things the script really needs.
You will need to make sure that you name your Excel Sheet that has the catalog data, Resource or change it in the script below. It can be called anything, they just have to be the same for the script to recognize where to look.
Ok here is the script! It is a VBA macro that you will need to run while in Excel. It may look ugly here, but pasted in it should have the tabs in the right space.
Let me explain how the script runs. It starts with rRow 1 (row 1) in the Resource worksheet and reads the Dewey number and Publication year. Then it cycles through the list of Dewey numbers on the Compare worksheet by looking at Row 1 and Row 2. If the number is greater than or equal to Row 1 and Less than Row 2, it uses Row 1. If it doesn’t match it will change Row 1 to Row 2 and Row 2 to Row 3 and try again, cycling through the entire resource alignment Dewey range until it finds a match.
If it does find a match, it uses the Keep and Discard years from the row that it found a match on to compare the Publication year from your Resource list. If the year is greater than or equal to the Keep year, it colors the row in your resource list with the books green. If it is less than or equal to the Discard year, it colors it red. Anything else (years that are less than the Keep year or greater than the Discard year) colors them yellow.
Once it finds a match and colors the row in your resource list worksheet it goes to the next book and starts the whole process over again.
Things you will need to change in the script.
You will need to change the 6929 to however many rows there are of books in your Resource worksheet.
For rRow = 1 To 6929
If your worksheet that you have the books on is called something else other than Resource, you will need to change all places where it says Worksheets(“Resource”) in the document.
BookDewey = Worksheets("Resource").Range("A" & rRow).Value
If you have the Dewey number for your books in any column other than the A column, this is where you would set the Column letter.
BookDewey = Worksheets("Resource").Range("A" & rRow).Value
Change the H to whatever row you have the publication year of your books in.
BookYear = Worksheets(“Resource”).Range(“H” & rRow).Value
Change the 505 to however many rows you end up with your resource alignment data worksheet.
For cRow = 1 To 505
If you have the resource alignment data Dewey number in a column other than the A column on the Compare worksheet, this is where you will change it.
DewCompVal1 = Worksheets("Compare").Range("A" & cRow).Value DewCompVal2 = Worksheets("Compare").Range("A" & cRow + 1).Value
Sub ColorBooks() 'Make sure the right worksheet is active Worksheets("Resource").Activate 'Rows that have books on them For rRow = 1 To 6929 'A column is the book Dewey Number BookDewey = Worksheets("Resource").Range("A" & rRow).Value 'H column is the book publication year BookYear = Worksheets("Resource").Range("H" & rRow).Value 'Rows that have resource aligment data - loop through entire resource aligment dewey list until we find a match For cRow = 1 To 505 ' "A" Column is where the resource aligment dewey numbers are DewCompVal1 = Worksheets("Compare").Range("A" & cRow).Value DewCompVal2 = Worksheets("Compare").Range("A" & cRow + 1).Value ' Compares the book dewey number to the the resource dewey number and the next number If BookDewey >= DewCompVal1 And BookDewey < DewCompVal2 Then GreatYr = Worksheets("Compare").Range("C" & cRow).Value PoorYr = Worksheets("Compare").Range("F" & cRow).Value Select Case BookYear ' If the book year is greater or equal to the Keep Year, color it green Case Is >= GreatYr Range("A" & rRow & ":Z" & rRow).Interior.ColorIndex = 4 Exit For ' If the book year is less than or equal to the Discard Year, color it red Case Is <= PoorYr Range("A" & rRow & ":Z" & rRow).Interior.ColorIndex = 3 Exit For ' If the book year is anything else (aka useful), color it yellow Case Else Range("A" & rRow & ":Z" & rRow).Interior.ColorIndex = 6 Exit For End Select End If Next cRow Next rRow End Sub
I enabled the Developer Tab in Excel to get access to the Macros. To enable the Developer tab you will need to go to File > Options > Customize Ribbon and check Developer:
You should now have a Developer Tab that looks like:
Click on Macros, type in a name and hit Create
Paste in the above code, it should look something like below. Make sure to change the script to reflect how your Excel document looks or it will not work or color it odd. If it does, you can always just reset the color. It only changes the row color and not any of the data.
Hit the play button
and you should see something like this: