Need to pull information from multiple workbooks, combine in one workbook, and then be able to filter out miscellaneous information

matt h 1 Reputation point
2021-03-24T15:02:31.197+00:00

I am still pretty new to VBA in Excel but have been tasked with what I believe to be a complex reporting assignment. For my companies' training department, I need to pull multiple training reports, have Excel be able to filter through these reports and pull relevant data to match and record on another report. For example: I would pull a report that lists all of the trainees' names, the specific training curriculum, and all trainings associated with that curriculum. For every curriculum and training, it lists the trainee's name in the sheet row. So if you have a trainee who has trained on 100 trainings, their name would be populated 100 times down the rows. This would repeat for every trainee so the spreadsheet could be in the thousands for entries. There is another report that can be pulled that lists all of the curriculums and their associated trainings (does not include any trainee information). What I am trying to do is create a template report, in which I can run a macro that would first pull all of the defined curriciulums and their associated trainings to the vertical axis of the report. It would then go to the first report, populate the trainee's names only once on the horiztonal axis, search the first report for that specific training code and that trainee name and then populate it in the template report as Trained or Not Trained. It would look something like this: ![81178-1-1.jpg][1] I was wondering if using INDEX, MATCH, or VLOOKUP would work but all I see them being able to do is look up a pre-defined search and populate that information. Where the gap lies is every time these reports are pulled, new users may be added and new trainings and their versions may be included. I cannot make the search specific to any one person or training since it will vary. I just need some guidance on how to even get started with this type of report as I am clueless at this point. [1]: /api/attachments/81178-1-1.jpg?platform=QnA