Highlighting Duplicate names in second file, after comparing to several sheets in first.

I know theres a way to do this, but I cant figure out how to get it to work automagically.

would it be a macro in a third file?

this is a daily procedure that is a minor PITA, and somewhat time consuming.

as an example: todays master file has 7 sheets. names are column 1, with 3 rows of header labels
sheet one: 115 names
sheet two: 37 names
sheet three: 3488 names
sheet four : 70 names
sheet five : 1771 names
sheet six : 1771 names
sheet seven 1771 names

second file has 70 names on one sheet, names are column 2, one row of header info.

the same names appear in several sheets on the master file.
(example: sheets 5,6&7 are often identical)


the goal is to automate the comparison of the second file (only has one sheet) to the first file (all sheets) looking for the same name.

any names existing in BOTH files should ONLY be highlighted in the second file.

save revision of second file with current date ammended to file name, then do the same thing tomorrow.

any ideas on where I should start?

Thank you for any suggestions!

J

Hi,

Could you attach a sample of the workbooks? It will make debugging and coding easier. Remove any sensitive information.

Also the second file that you are comparing to where is it located in a specific location? or varies?

Thanks

7 sheets within the first file are representative of 7 different divisions.

apparently someone combines 7 files monthly to make this task a little less irritating for me..



as far as posting, the relevant data is simply a column of names in relation to their retirement/departure/firing date, so there really isn't much i can post as an example that would be useful for debugging..
there is a great amount of extraneous data, and all I'm interested in are name matches, which are in the form of "last,first" within the same cell.

after inquiring, apparently a list of the duplicated names would work nicely, as opposed to just highlighting names within a duplicate of the second file.


basically file 1 is a monthly list of everyone who has access to a particular company resource,
file 2 is the list of people leaving the company for whatever reason, who may or may not have access to said resource.
the idea is to find the people leaving who DO have access/accounts and pass that filtered info to the relevant admin to remove their accounts from the system.
(yup- i know its a little bit of a mess, but its what i get to deal with)

Sanitized for your protection:Main File.xlsxSecondary file.XLS

Again- any assistance is greatly appreciated!

T

I think I found a way to make sure there are no mistakes with names by instead comparing the email addresses.
I'm still trying to figure out how to make a third file with ONLY the rows that have email addresses that match, consolidated so that I can see all duplicates without scrolling thru the list.