Merging Data from single worksheet in multiple workbooks

[taylorsm]

My team is responsible for performing audits. In order to keep track, I have an audit tracker sheet. Issue is how to manage 8 worksheets into one and not duplicate or lose data. I have a sheet that has the roster of all teams/employees. In my attached workbook it is Manager1/Manager6/Manager11/Manager16. Auditor 1-4 have their individual teams they audit and log their findings. I need to then combine that data into a master. Each auditor has their info in their own workbook. A shared file doesn't work, that is what I had before and it was nothing but problems.

To throw a wrench in it, teams change. So Auditor1 may have Rep1-5 this week, next week Auditor1 may have Rep 1,2,10,15,9. So how do I pull the data together? Copying each will just cause blanks to overwrite data.

I think if I have a additional column, where I have the auditor's name, then can I tie the macro into looking into Column "A" and only copying that row?

Can anyone shed light on a better way? Otherwise I'll just use a macro that searches column A for the Auditor's name.\

[Olly]

Post a couple of samples of the source workbooks that you want to merge, and what the expected output (consolidated view) looks like.

It should be simple, using Power Query.