Parse a column in one workbook, compare with data in another and make a selection

[Dubs]

So, I was literally handed 30 workbooks, each with 11 worksheets, full of names, dates locations etc... and was asked to 'simplify' it...

I've built a workbook that will contain all of the data on one worksheet vs. 330, but in the interest of saving time, I want to figure out how to manage automated transfer of the data with a comparison and selection based on a Boolean value.

I have attached two workbooks

1. WhatIWasHanded: is an exact match of one workbook, only 3 worksheets instead of 11. The worksheets all have the same UNIT LOCATION (i.e. EMERGENCY ROOM) but the class attendees (NAMES) are not equal on every worksheet.

2. WhatImBuilding: This is the workbook I'm building, the framework is exact, only the names are changed

I am simply copy / pasting names into my workbook as I move the 30 workbooks I was handed; however, filling in the 'YES' / 'NO' values on each piece of equipment is a headache and is taking way too long, as I have to do a name comparison on each worksheet for each piece of equipment.

Here is what I would like to do:

I want to parse the information from column A, one worksheet at a time, from the 330 worksheets I was handed. I then want to compare the strings with the strings in column A of my workbook, If the name is on both sheets = 'true' then I want the corresponding(worksheet Equipment 1, column G 'Equipment 1' on my worksheet) 'YES' / 'NO' value to auto select and fill.

Any help would be very greatly appreciated, from both me and my sore, bloodshot eyeballs....

WhatIWasHanded.xlsx

WhatImBuilding.xlsx

[HaHoBe]

Hi, Dubs,

could you please explain where the locations and positions come from? And I think there may be a small problem as by now you have only one column with a date whereas I think there should be a date for any equipment.

Do you copy over the names into the master in advance or do you want the code to fill in whatever is known? Is maybe the name of the file the location which should be filled in?

One possible scenario to solve the problem: all workbooks are in one folder, opened in a loop and another loop works through all the worksheets inside the newly opened workbook. The last line with data for any worksheet will be taken as the limit of cells to run on. Any name in the data range will be search in the Mastersheet, if found a range is set to that name in MasterSheet. According to the equipment name the date will be inserted and a Yes will be placed.

Problem I see here is a name like John Smith. If you could use an employee number or the social insurance number it would make the search look just for one possible match instead of more than one which must be narrowed down by location and/or position.

Ciao,
Holger