Tools
In this Guide you will use:
- Destiny to run the current checkouts/fines report,
- Google Sheet with summer school students listed,
- MS Excel to merge Destiny report and Google Sheet list into a report displaying students who are not attending summer school but have not returned the district-issued equipment yet.
Steps
Step 1: Destiny
Run the current checkouts/fines report in Destiny with the following settings:
Download the Excel file once it's ready in the Job Manager.
Step 2: Excel, Prep Work
Open and prepare the Patron Circulation report for the merging by removing "P " from the Patron Barcode column. Highlight the whole column with student IDs by clicking on the letter name of the column (B in this case). Bring up the Find-Replace screen by pressing Ctrl+H.
Find "P " (with the space!) and replace with a blank. Replace All.
Create a new sheet in the same file by clicking + next to the existing sheet name. This sheet will hold summer school students' IDs.
Step 3: Google Sheet, Copy
Open the shared Google Sheet workbook containing summer school students lists and copy all student numbers on the Master List sheet.
Step 4: Excel, Paste
Paste the copied list of IDs into Sheet1 (if you haven't renamed it) cell A1 of the opened Excel file. To do that, right-click into A1 and select Paste Special > Match destination formatting.
Step 5: Excel, Fun with Formulas
On the Patron Circulation report sheet, right-click on column D, for example, and select "Insert column" to insert a blank column to the left of the grade level column. Type in the VLOOKUP formula starting with the equal sign. If you are following steps in this guide, then your formula will be as follows:
=VLOOKUP(B2,Sheet!A:A,1,FALSE)
Hit Enter to apply the formula.
As the screenshot above states, the formula is made of 4 parts:
- B2 is the first lookup value you have on your sheet - the value that you want Excel to find on the Sheet1 that has all summer school students' IDs. Generally, if you're entering the formula into the row 2 of cell C, then your lookup value is also in the second row;
- Sheet1!A:A is the sheet name and cell range reference: you're making Excel look through Sheet1's range A:A (which means the whole column A). ! is used to separate the sheet name from the range. : is used to separate the beginning of the range from its end (top-left cell from bottom-right). To reference the whole column or columns, omit the row number: A:A or A:Z, and so on. Referencing a specific limited cell range will look something like this: A2:B6 or D123:Z1000. The most important thing to remember is that the column with the referenced value (student ID) needs to be the first one in the range;
- 1 is referring to the sequential number of the column that you want the formula to return. In this case we only have one column in the referenced range, so 1 it is. If it was something like A:B with B containing student names, and if you wanted to return student names to the main list, then you'd list column 2;
- FALSE just means that NO - you DON'T want approximate matching (a.k.a. best guess if nothing is found). You want exact matches.
Now that you have an active formula for the first student in your list, you can flash-fill or click-drag that formula for the rest of the students.
Step 6: Excel, Filter
Turn the filter on for the whole sheet by selecting all content: either Ctrl+A or click on the corner block between the first row # and the first column name (left of A, top of 1) - then switch to the Data tab of the workbook and click on the funnel icon to turn the filter on.
Click on the drop-down icon next to your column with the formula, and either Sort A to Z (or Z to A) to group all #N/A together, or filter for the #N/A value. #N/A is the code returned by VLOOKUP that means it was unable to find a match for this student ID on the summer school students sheet. Meaning, these students are non-summer school students, and if they appear on the checkout report, they still owe you some equipment. These are the students to contact and iPads to lock/mark lost.