1
The first step is to open your exported Blue Cloud report in Excel. You will be adding a column next to the Call number column. This is where the sortable call numbers will go in order to convert the report to proper Library of Congress order.
2
Right Click on Column C.
3
Click Insert
4
Right Click on Column B. You will be copying and pasting the Call number column into a new Excel document to sort.
5
Click Copy
6
Right Click in Column A in new Excel document.
7
Click Paste. This will paste the Call numbers from the Report into the new document.
8
Press Ctrl+C . This will copy the first formula. Next you will paste the formula in cell B2 in the new document.
9
Select the B2 cell.
10
Press Ctrl+V in Cell B2 to paste the first formula. Use the corner box to copy the formula for the rest of column B. Doubleclick the corner box to copy the formula down the column.
11
Select Cell C2. This is where the 2nd formula will be pasted.
12
Press Ctrl+C to copy the 2nd formula. (Both formulas are necessary to convert the report into call number order.)
13
Press Ctrl+V. This will copy the 2nd formula into Cell C2. Again,
use the corner box to copy the 2nd formula for the rest of column C. Double click the corner box to copy the formula down the column.
14
Time to fix errors. One common error is a space between the first number and the
period in the call number. To
solve this issue: Use Find and Replace in Excel. Search for “ .” and replace
with “.”
15
Click Find & Select
16
Click Find...
17
Type a space and a period, Then Click Replace .
18
Type a period and Click Replace All
19
Click OK. This is an information screen letting you know how many errors were fixed.
20
Now that punctuation errors are fixed, copy Column C. Right Click.
21
Click Copy
22
This column of call numbers will be copied into Column D and pasted as values only.
23
Click Values
24
Click on Column D.
25
Type "Sortable Call Numbers" in Column D.
26
Column D is now ready to be copied and pasted into the original report. Click Copy
27
Right Click in Column C of the original report (the column next to the original call number column.)
28
Click Paste
29
Now it is time for Sorting! Click on one of the cells in the sortable call number column.
30
Click Sort & Filter.
31
Choose Custom Sort.
32
Click on the scroll bar to select which item to sort by.
33
Choose Sortable Call Numbers
34
Click OK
35
You will see that the report is now sorted in Library of Congress Call Numbers.
Notice that the highlighted line has moved it's location. This is because that entry is now in correct call number order.
36
That's it! The practical applications of converting reports to LC order include collection development reporting, day to day shelf reading and, of course, Inventory.
