Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Analytics report sorting items by LC Call Number

    • Article Type: General
    • Product: Alma

    Desired Outcome Goal:
    Creating an Analytics report to list call numbers and barcodes for one of our collections, the LC call number should sort so that if a letter comes after the decimal point, it will be listed before a call number that has a number after the decimal point, e.g., PN1997.A1 G467 2008 DVD should come before PN1997.2 .W54 2003 DVD. Sort by ascending and descending doesn't achieve this.

    * Customize a canned (out of the box) report; it sorts LC Call Numbers correctly.
    * Found in: /Shared Folders/Alma/Inventory/Reports/Detailed Item Count by Library and Location

    Additional Information

    The following procedure can resolve this issue:

    * Import data [...] into Excel.
    * Move column containing call numbers to column A.
    * Insert blank columns for Column B and C. If your file has headers, name them “Intermediate” and “New Call Number” respectively.
    * Copy the part 1 formula in B2. Use handle in lower right corner to drag down to end of column.
    * Copy the part 2 formula in C2. Use handle in lower right corner to drag down to end of column.
    * Select call cells and click on Customize Sort in Excel and select the sort column to be "New Call Number" (or column C if you have no headers).
    * Please note this formula does only the base call number and will not sort volume numbers, issue numbers, or sheet numbers.

    The formula:

    =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))))

    (ExL: Copy and paste the new column using Paste-Special > Values; this will enable to copy the information to other documents without losing information)

    Category: Alma analytics - Alma

    Subject: Analytics

    • Article last edited: 5/6/2014