Hold Request Analysis Report
Use these steps to identify holds placed by patrons of a specific branch on titles that the branch does not own.
Step 1: Run the Report
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:
In Excel, open the report and create new column I labeled “Match”
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,“”)
Double click the bottom-right corner (the tiny square) of cell I2 to fill the entire column with the formula
Column I should now contain the Record ID when there is a match
Part 2:
Click Conditional Formatting (on the Home Menu Bar) > New Rule
Select “Use a formula to determine which cells to format”
Enter the formula as: =COUNTIF($I$2:$I$100000,$E2)>0
Click the format button to determine the format to apply (I like to choose Fill of gray)
Click Okay
Click Conditional Formatting again, then Manage Rules
Change the “Applies to” field to: =$A$2:$I$100000
Click Okay
Each row that is a match should now be colored gray
Part 3:
Click Conditional Formatting (on the Home Menu Bar) > New Rule
Select “Use a formula to determine which cells to format”
Enter the formula as: =COUNTIFS($B$2:$B2, $B2, $E$2:$E2, $E2)>1
Click the format button to determine the format to apply (same color as the other rule)
Click Okay
Click Conditional Formatting again, then Manage Rules
Change the “Applies to” field to: =$A$2:$I$100000
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.
Click Okay
Each other copy that has the same Record ID as a matched copy should now be colored gray
![](/dokuwiki/lib/exe/fetch.php?w=700&h=255&tok=3dda69&media=reports:holds:screenshot_-_conditional_formatting_rules.png)
Conditional Formatting Rules
Step 3: Sort or Filter the Results
Depending on how you prefer to work, you may either sort or filter the results.
Sorting
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.
Select the complete spreadsheet by clicking the small triangle at the intersection of column A and row 1
From the Home Menu Bar select Sort & Filter > Custom Sort
Add levels to sort by cell color (No Cell Color), then Count of Hold Requests (Largest to Smallest), then Title (A-Z)
Filtering
Use these steps if you'd rather filter the results to hide the titles you aren't interested in completely.
Go to Data menu bar and click Filter (Note - this process can be slow)
Click on the little down arrow on column A
Choose the Filter By Color option
Select “No Fill”
Example Report Output
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