Automating Resource Alignment with Excel

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 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 in the chart, find the Dewey number, and then look at the year to help you make your decision on the book. 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:

  1. Lowe’s Resource Alignment data in an Excel form.
  2. 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.
  3. 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.

Resource Alignment table converted to Excel
Resource Alignment table converted to Excel

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.

Your Collection

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

=LEFT(A2,FIND(" ",A2)-1) 

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.

The Script

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: