How to Validate Data after Someone Messes it up

[Goodspeed]

Hi Everyone. I hope you are all doing well. I am looking for help on a problem I am unable to resolve. I have searched for days and just not finding a resolution. Here is an example of my issue. The problem happens all the time for many of my processes but I tried to use an easy example so that I could be concise.

I generate a list of current locations we have workers at. I send that out to my teammate requesting her to update a column with the # of people that are at each location. I then need to take those numbers and create a PowerPoint slide for a meeting. What always happens is..... she updates the list, but while doing so she mis-sorts (by not selecting the entire worksheet) so that when I get it back the addresses are not aligned with the correct locations. So now, I'm not sure if the total headcounts are lined up with the correct locations since she messed it up.

What I do today - is insert 2 columns after every single column - in the first column I do a vlookup to bring back the original data from my master file. In the second column I do an if/then to determine if my original data matches the new data. Its so tedious. it wouldn't be bad if i had a handful of columns, but sometimes I have 20 columns. Essentially - I want to validate when they send the sheet back, my data wasnt changed. (I cant lock cells because they do need to sort and filter, etc.)

I have found some examples with comparing columns.. but the issue is that they wont always be in the same rows and the number of rows could change anytime. I need to first match on the location code, and then validate ALL the other cells in that row for that particular location code.

I'm learning VBA and have created a few small macros but this one is really stumping me and I just cant find what I'm looking for anywhere. I've searched the forums multiple times - so I apologize if this answer is out there, but I couldn't find it.

I'd be appreciative for any help you could provide. I attached a sample file with 20 rows of fictitious data - hoping it helps. Thank YOU so much.

[AliGW]

Welcome to the forum.

To me, the solution seems obvious: send the data to the lady in a structured table format. By doing this, it won't matter how she sorts the table, the data will stay as it should.

To turn your ranges into structured tables, select the whole range and choose Insert | Table. Add a column ready for the headcount to be added.

[davsth]

Can you explain what her sorting is. My suggestions would be either

I would make a button to do her sorting and add the vba code to it, so it stops the chances of her forgetting.

Or which may be simpler, send her the data as a table, will the column she needs to populate blank. Then sorting by default includes the whole table

[davsth]

Ali and I agree! she beat me by seconds!

[CK76]

My recommendation. Instead of cleaning up after the fact, use tools available to make it much harder to mess up sort.

Ex: Always send initial list to your co-worker as Excel Table and not as regular range.
This way, all sort operation will be done on table as whole and row will never be out of sync.

Edit: As others above have already suggested.

[Goodspeed]

thank you.. you guys are so quick.. (and the experts so probably right).. however, that was just a simple example that I could think of to share. lol - sorry. I send data out to my peers all the time (not just this one person) - and they add to it and send it back... and sometimes also mess it up. Sometimes its not just location. Sometimes its sensitive data. They may send to others to add to or populate. So I guess I need a way to validate what I sent them comes back the same way.. I can and will absolutely try your approach.

But one small problem... I'm awaiting 25 sheets back and didn't do what you recommended (since I sent them out before i found this forum - bummer) - so any ideas on how I can at least check the ones that will be coming back to me this week thats quicker than what i'm doing today?

p.s. why cant people just learn how to use excel correctly.

[JeteMc]

Perhaps this is an oversimplification but from the sample it would seem that if the headcount either matches the location code or the location name or both.
If the same headcount matches both then the number stays in that row.
If there are different numbers in the same row then send the file back for clarification.
The formula used to find the matching headcount is: =IFERROR(INDEX('Sheet that was returned'!$H$2:$H$50,MATCH(Table1[@[Location Code]],'Sheet that was returned'!A$2:A$50,0)),"")
Note that the Main list of locations has been converted to a table.
Let us know if you have any questions.