Tracking daily compliance and need assistance with pairing de-identified information

[HarrisonNutrition]

I am looking for help with a vitamin supplement compliance tracking spreadsheet.

We have 2 different documents we work off here.  

Spreadsheet A contains HIPAA information including name and lab values.  Each name on this spreadsheet is assigned a container (Ex. Swim1, Swim2)

Spreadsheet B is de-identified and is used by employees to track compliance.  They do this with a barcode scanner that they scan athletes that are non-compliant.  The barcode scanner links directly to our spreadsheet B, and the non-compliant containers (again, Swim1, Swim2, etc.) are scanned in "randomly" (meaning not necessarily in the same order every night).  We currently have the scanner to move horizontally across columns when scanning to allow the first column to organize by date.  Our scanner has the capacity to scan vertically if needed.  This scanning is done on the "Scan Input" tab.

We are hoping to be able to build a report that will easily allow us to view who has been non-compliant >20% of the time.  Ideally we would like this report to pair the last name (from spreadsheet A) with the container compliance on spreadsheet B. Currently we have a pivot table set up for each team but this doesn't look very clean/functional.  We also don't know how to tie in the names from spreadsheet A to this pivot table.

In summary, I'm looking for a suggestion to clean up this data summary (we follow up with anyone who has been non-compliant >20% of the time) both in the visualization of it on excel/pivot tables/etc and in being able to sync the last names from Spreadsheet A to the report generated in spreadsheet B. 

Link to sheets here:

https://1drv.ms/x/s!Am4qCs1ai-TLoDhsHSdb2VXYwCQd?e=rArV3G 

[#2]

@HarrisonNutrition 

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!

[#3]

 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

@NikolinoDE