Forum Discussion
Tracking daily compliance and need assistance with pairing de-identified information
Without opening the file (for personal security reasons), one-way to clean up the data summary and sync the last names from Spreadsheet A to the report generated in Spreadsheet B is to use a VLOOKUP function.
This function can be used to search for a value in one column of data and return a corresponding value from another column.
In this case, you can use VLOOKUP to search for the container name in Spreadsheet B and return the corresponding last name from Spreadsheet A.
To do this, you'll need to make sure that the container name is the first column in Spreadsheet A and that the last name is in a column to the right of the container name.
Then, in Spreadsheet B, you can use a formula like `=VLOOKUP(container name, Spreadsheet A!A:B, 2, FALSE)` to return the corresponding last name.
As for visualizing the data, you could consider using conditional formatting to highlight cells that meet certain criteria (e.g., non-compliance >20% of the time). This can make it easier to quickly identify which athletes need follow-up.
I hope this helps!
We currently use a pivot table to summarize our data, is there a way to make VLOOKUP pull the names to put into our pivot table?
As we scan them in, they get inputted randomly across columns B-MV (Column A is our date column). Depending on compliance for the day we use only a fraction of those columns.
The only solution I can see here using VLOOKUP, would be to create a second table and have each formula be a VLOOKUP referencing the scan input cell table for that same location? I'm wondering if there is another more efficient way.
Below are some screen shots of our document:
Scan Input page
(Container IDs get scanned daily, order is random, and total count depends on compliance, we have our table set out 360 columns to allow for the potential of 360 misses)
Example of a compliance tracking page (for swim)
Like I mentioned before, our barcode scanner has the ability to scan horizontally or vertically, so if you can think of a solution that would work more efficiently scanning down 1 individual column each day rather than across we can update that too! Thank you
