I'm working with spreadsheets containing a large amount of data. Right now, it's pretty manageable, and most of my basic analysis work can be done manually. I'm thinking long term, where instead of 50-100 unique cases all of whom have their own sets of recording data, I'm going to have 1500-2000.
I'm pretty new to pivot tables, and understand the basics, but am having trouble with what I'm currently trying to do.
In the attached spreadsheet (I get sent an updated one every 2 weeks), there's a lot of columns! The unique identifier I primarily use when filtering data is Column G, the LRFID. The unique recordings I look at are labeled in Column Q, "RecUseNumber."
The primary categories/scores/numbers I look at are "AWC" and "CT,"(Columns S thru AL) and more importantly, changes in those groups from their initial baseline recording, and their most recent one, using a specific formula. It's very easy to get the average baseline data, because all I need to do is filter out "RecUseNumber = 1". It's more complex to try and get the most recent recording data, because for some cases their most recent data comes from RecUseNumber = 8, and others it may be RecUseNumber = 13. I can easily scroll down and manually click the most recent scores by looking at every one, but this gets tedious, and will be impractical when the data set grows larger.
As an example, what I've been trying to do is get the most recent recording data for Column V, AWC_Change, for every single LRFID. Every recording has a date (Column M) but I can't figure out if there's a way to use the pivot table to filter out ONLY the most recent sets of data.
Any help is appreciated. When using the spreadsheet, use the "Recording Data" tab at the bottom. The other 3 tabs can be ignored.
EDIT:
I can get as far as this -- using the pivot table, I enter in LRFID as the Row Label, and for Values, use the "Max" value of RecordingDate. That gives me the most recent recording date for every unique identifier, but I can't figure out how to progress past that.
data export 2_18.xlsx
Bookmarks