Sometimes the data you download into an Excel spreadsheet doesn’t have everything you need on it. One of our programs wanted to be able to analyze ISP Data according to the Case Manager for each client. However, Case Manager names are not kept in ISP Data records – only DSPs.
Here’s how we solved it:
1. Create a new tab in your Excel file opened from the Therap download
2. Name that new tab (sheet) “Lookup”.
3. In the Lookup sheet, copy a table/list of clients and case managers into the first two columns – with client names on the left hand column and case managers on the right. NOTE: Client names have to match exactly what’s in the Therap download.
4. It would look like this:
|Barley, Mow||Freeman, Rebecca|
|Briney, Cream||Eckerson, David|
|Sailing, Ship||Harper, Stacey|
|Lovely, Girl||Freeman, Rebecca|
|Sweek, Pea||Freeman, Rebecca|
|Chocolate, Pie||Freeman, Rebecca|
|Loveable, Harry||Eckerson, David|
|Smith, John||Harper, Stacey|
|Brown, Jane||Eckerson, David|
|Fir, Tree||Harper, Stac|
5. Now in the Data sheet downloaded from Therap, click on any cell except the column header, and do Insert >> Table.
6. Next, insert a column to the right of the client’s name and label the column “Case Manager”.
7. In the first vacant cell in the Case Manager column, type the following formula:
=VLOOKUP(B2,Lookup!$A$YY:$B$XX,2,FALSE) where B2 is the cell holding the first client name in the Therap download, and in the lookup table “YY” is the row number of the first row of the client/case manager list and “XX” is the row number of the last row of this little table with client/case manager names.
This should automatically fill up the Case Manager column with the proper Case Manager names.
8. Now create a Pivot Table and go have fun.