Use these steps to identify holds placed by patrons of a specific branch on titles that the branch does not own.

The report template is located at:

Shared Folders > Templates > terran > Help Desk > Holds > List of holds placed by my patrons with available sources

Notes:

• You may choose one or more pickup locations in your system.
• The formulas below are limited to 100,000 rows - if your result set is larger, you will need to modify the formulas. Keep in mind that Excel will require more time and processing power to process the formatting and instructions and may crash if the data set is too large.

Part 1:

1. In Excel, open the report and create new column I labeled “Match”
2. In cell I2 enter the formula (“WGRL-*” will look for copies in any WGRL branch, or you can use the format “WGRL-DR” for a specific branch): =IF(ISNUMBER(SEARCH(“WGRL-*”,G2)),E2,“”)
3. Double click the bottom-right corner (the tiny square) of cell I2 to fill the entire column with the formula
4. Column I should now contain the Record ID when there is a match

Part 2:

1. Click Conditional Formatting (on the Home Menu Bar) > New Rule
2. Select “Use a formula to determine which cells to format”
3. Enter the formula as: =COUNTIF($I$2:$I$100000,$E2)>0
4. Click the format button to determine the format to apply (I like to choose Fill of gray)
5. Click Okay
6. Click Conditional Formatting again, then Manage Rules
7. Change the “Applies to” field to: =$A$2:$I$100000
8. Click Okay
9. Each row that is a match should now be colored gray

Part 3:

1. Click Conditional Formatting (on the Home Menu Bar) > New Rule
2. Select “Use a formula to determine which cells to format”
3. Enter the formula as: =COUNTIFS($B$2:$B2, $B2, $E$2:$E2, $E2)>1
4. Click the format button to determine the format to apply (same color as the other rule)
5. Click Okay
6. Click Conditional Formatting again, then Manage Rules
7. Change the “Applies to” field to: =$A$2:$I$100000
8. Make sure that the first rule you created is on top (use the arrows to re-order if needed) and has the “Stop if True” checkbox checked.
9. Click Okay
10. Each other copy that has the same Record ID as a matched copy should now be colored gray

Conditional Formatting Rules

Depending on how you prefer to work, you may either sort or filter the results.

These steps will sort the titles of interest to the top of the sheet with the titles with the most hold requests first, then in order by title.

1. Select the complete spreadsheet by clicking the small triangle at the intersection of column A and row 1
2. From the Home Menu Bar select Sort & Filter > Custom Sort
3. Add levels to sort by cell color (No Cell Color), then Count of Hold Requests (Largest to Smallest), then Title (A-Z)

Custom Sort

Use these steps if you'd rather filter the results to hide the titles you aren't interested in completely.

1. Go to Data menu bar and click Filter (Note - this process can be slow)
2. Click on the little down arrow on column A
3. Choose the Filter By Color option
4. Select “No Fill”

This example shows the results for title-level holds placed for pickup at WGRL-DR in March, 2024. The spreadsheet is formatted to emphasize the 286 the hold requests for titles not owned by any WGRL branch. Only one of the 286 titles was requested more than once during this time period. The first tab is filtered and the second tab is sorted.

sample_report_-_identify_hold_requests_for_items_my_library_does_not_own.xlsx