Sorting by Library of Congress (LC) call number in Excel is problematic because Excel does not treat LC call numbers as numbers. In practice, this means that Excel sorts LC call numbers based on each successive digit in the call number rather than the overall value of each call number. (for example, a call number beginning with PS507 would be sorted after one beginning with PS4998 in a default Excel sort). In this presentation, I demonstrate one method for accurately sorting by LC call number in Excel

Jolley, Daniel W.

English

Digital Commons @ Gardner-Webb University

Gardner-Webb University Digital Commons @ Gardner-Webb University Dover Library Faculty Professional Development Activities John R. Dover Memorial Library 5-24-2023 Sorting by LC Call Number in Excel Daniel W. Jolley Gardner-Webb University, djolley@gardner-webb.edu Follow this and additional works at: https://digitalcommons.gardner-webb.edu/doverlibfacpub  Part of the Scholarship of Teaching and Learning Commons Recommended Citation Jolley, Daniel W., "Sorting by LC Call Number in Excel" (2023). Dover Library Faculty Professional Development Activities. 20. https://digitalcommons.gardner-webb.edu/doverlibfacpub/20 This Presentation is brought to you for free and open access by the John R. Dover Memorial Library at Digital Commons @ Gardner-Webb University. It has been accepted for inclusion in Dover Library Faculty Professional Development Activities by an authorized administrator of Digital Commons @ Gardner-Webb University. For more information, please contact digitalcommons@gardner-webb.edu. Sorting by LC Call Number in ExcelDaniel JolleySystems LibrarianGardner-Webb UniversityWorking with WMS inventory lists• I routinely pull inventory lists for different sections of the library• We use inventory lists for weeding as well as the inventory process itself• Librarians and staff members want and need for these lists to be sorted by call number• Having inventory lists with significant numbers of items radically out of LC call number order is a problemThe problem• Excel’s sorting rules do not work particularly well when it comes to sorting by LC call number• In a standard Excel sort, BT 5 comes after BT 4999, for example, and PR111 comes after PR1109• This happens because Excel is seeing LC call numbers as “text-numbers” and sorting based on the digits in each number rather than the overall value of each numberPR105 .J58PR105 .M3 1967PR106 .B66 2008PR106 .B7 1970PR106 .R55 2007PR1109 .A2 2000PR1109 .A2 2000PR1109 .A2 2006 v.2PR1109 .A2 2006 v.3PR1109 .E63 2006PR1109 .L67 1999PR111 .E94 1993PR111 .W52PR1110.W6 F4 1977PR1110.W6 G6PR1110.W6 L39 1997Other possible problems for sorting• Some old records have a space between the opening letters and opening numbers of call numbers• A significant number of older records have an extra space in front of the first period in the number (e.g., PR1109 .A2 2006 rather than PR1109.A2 2006)The solution• The method I use to sort LC call numbers is not one of my own creation.• The basic method was originally posted in 2014 on Penn State’s institutional repository site by Lee Ann Nolan and Jameson Conley in the form of a 1-page PDF.  The most current version of the document is available at https://scholarsphere.psu.edu/resources/4e1e5dc2-6251-4ff2-8658-ce90b8db6180• I have added a few tweaks to the process based on my experience• The method is not perfect, as it only sorts by the base call number (it does not sort by volume numbers or the like) – but it definitely beats having the likes of PS203 books immediately following PS2029 books in your “sorted” inventory listSorting by LC call number• In order to use the two formulas involved in this method, your call numbers need to be in Column A (otherwise, you would need to edit the formulas to account for the column difference)• The first formula basically converts the number immediately following the letters to a four-digit number (BS95 becomes BS0095)• The second formula “inserts a ‘.0’ second cutter to those call numbers without one (e.g., turning G0001.A into G0001.0.A to ensure it sorts before G0001.1.A)” (Nolan & Conley, 2016)• The values generated by the first formula go into one newly inserted column, while the values generated by the second formula go into a second newly inserted column.Isolate the data you want to work with• Isolate the section of the downloaded inventory report that you want to work with (e.g., eliminate withdrawn items, sort by shelving location and call number to isolate a certain range of items, etc.)• If you are working with only a section of the full inventory report, copy and paste that section into a new Excel spreadsheet• Copy and paste the header row into the top row of the new spreadsheet• Move the Item_Call_Number column to Column A• I normally just delete the first five columns, as I typically do not need them• You could also just copy and paste the call number column into Column A, then delete that duplicate call number column after you finish sortingInsert temporary columns• Insert three columns immediately after Column A• I like to ensure the width of column A is wide enough to show all call numbers in their entirety before inserting the three additional columns, as the newly inserted columns are going to be the same width as Column A• You can add headers to the three new columns if helpful• I tend to just add the header “New Call Number” to column D to make it easier to find in the sort options later• Columns B, C, and D are going to be temporary columns used in the conversion processInsert temporary columnsApply Formula 1 to Column B• Insert the following formula into cell B2:• =CONCATENATE(LEFT(A2,IF(ISERR(VALUE(MID(A2,2,1))),2,1)),REPT("0",5-FIND(".",A2)+IF(ISERR(VALUE(MID(A2,2,1))),2,1)),RIGHT(A2,LEN(A2)-IF(ISERR(VALUE(MID(A2,2,1))),2,1))) • Select cell B2.  Hover over the bottom right corner of the cell to display the handle icon, then click on that handle and drag down to the end of the column• This will convert the first part of the call number to four digits (with preceding zeros where needed)• Do not worry if you see #VALUE! rather than a call number in any cells – we will fix that shortlyFormula 1 appliedApply Formula 2 to Column C• Insert the following formula into cell C2:• =IF(IF(ISERROR(FIND(".", B2, 1+FIND(".", B2))<FIND(" ", B2)), IF(ISERROR(FIND(".",B2, 1+FIND(".",B2))), FALSE, TRUE), FIND(".",B2, 1+FIND(".",B2))<FIND(" ", B2)), B2, CONCATENATE(LEFT(B2,FIND(".",B2)),"0.",RIGHT(B2,LEN(B2)-FIND(".",B2))))• Select cell C2.  Hover over the bottom right corner of the cell to display the handle icon, then click on that handle and drag down to the end of the column• This will insert a .0 after the first set of numbers without one• Do not worry if you see #VALUE! rather than a call number there – we will fix that shortlyFormula 2 appliedReplace extra spaces in original call numbers• You may see #VALUE! errors in some cells• The most common cause of the #VALUE! error is a space immediately following the first period in the call number• BS305 .H6813 instead of BS305.H6813, for example• To correct these, select Column A and use Find and Replace to replace “ .“ (a space followed by a period) with “.“ (a period by itself).  Use Replace All to apply this to all call numbers in the column.• There may still be some cells display the #VALUE! error, but it will address those later in the processPaste and sort Column C values into Column D• In order to sort our newly modified call numbers, we need to paste the values from Column C into Column D.  • Select all of the values in Column C (starting with cell C2)• Do not select the entire column – just the cells with values in them• Paste this in Column D starting with cell D2• Paste Values only – do not do a standard paste as this will bring the formulas over rather than just the values.  • Now sort Column D• I select the entire worksheet, go into Data – Sort and choose to sort by New Call Number (Column D if you did not name that column)• Make sure that the “My data has headers” box is checked in order to see header names in the Sort by box.  Fix any remaining #VALUE! errors• Any remaining cells with the #VALUE! error should all be grouped together now at the bottom of the worksheet• If any of these are missing a period, edit these manually• E.g., B49 P4 needs to be B49.P4• This will automatically “fix” the values in Columns B and C but not D.  For each of these, copy the cell in Column C and use Paste Value to post that call number into the respective cell in Column D• I like to sort by Column D once again after making any of these fixes• Any remaining #VALUE! errors are likely due to old-style call numbers (e.g., B1330 1942) in the system that cannot be “fixed” completely• Select the values for these call numbers in Column A and user Paste Value to paste those manually into the respective cells in Column D• For any of these call numbers with less than four digits immediately following the initial letter(s), add any preceding zeroes necessary to convert those into four digits in Column DFix any remaining #VALUE! errorsRe-sort Column D and remove temporary columns• Sort by Column D one last time• All of the records in your spreadsheet should now be sorted correctly by LC call number• Delete Columns B, C, and D to leave only the call numbers in Column A• Remember that volume numbers and copy numbers will not necessarily be sorted in the correct orderList now properly sorted by LC call numberSourcesNolan, L.A., & Conley, J.  (2016).  Call number sorting in Excel.  ScholarSphere.  Penn State University Libraries. https://scholarsphere.psu.edu/resources/4e1e5dc2-6251-4ff2-8658-ce90b8db6180PDF Summary Document• You can find a PDF summary of the steps outlined above at the following URL: • Sorting by LC Call Number in ExcelContact InformationDaniel JolleySystems LibrarianJohn R. Dover Memorial LibraryGardner-Webb Universitydjolley@gardner-webb.edu704-406-2109

Sorting by LC Call Number in Excel

https://digitalcommons.gardner-webb.edu/cgi/viewcontent.cgi?article=1019&context=doverlibfacpub

Sorting by LC Call Number in Excel

Abstract

Similar works

Full text

Available Versions

Digital Commons @ Gardner-Webb University