Identify multiple repeat values across multiple sheets in a workbook

Copper Contributor

I create a quarterly Excel sheet of employees who click on phishing test emails I send out.


The sheet has a column that has a unique value for each employee (email address) . Every time I run a phishing test I get a new sheet. I have two so far but this is going to continue for the foreseeable future.


I need to show employees who repeatedly show up across multiple phishing tests.


Ideally I would also like to know how many times an employee is on each sheet.

2 Replies


As variant you may collect the tests not in separate sheets but in separate files collected in some folder. With that using Power Query in another file you may combine all these files (From Folder connector) and build reports required, e.g. GroupBy userId and count number of records or like. 

Thank you sir. If it's need, then I am open to doing this a different way in order to get the results I need. Thank you for the fresh perspective.