Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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.