What excel formula can I use for this?

Hello, 

I need to work on a report for work where I need to get 80 (yes, 80) spreadsheets that has 4 columns "Email, Name, Position, Department) and cross reference them with each other to see if the same person is showing up on multiple reports and if so, how many? Is there an excel or Google sheets formula I can use for this?

If I need to I am fine with merging all spreadsheets into one if it makes it easier. 

3 Answers

Relevance
  • JASON
    Lv 6
    1 month ago

    You could uniquely number each line of each spreadsheet, 1 to 100, 101 to 200 or however long they are. Copy and paste values each list one above the other on a new spreadsheet, then sort by name. The same names will be grouped together, so you could conditionally format to highlight duplicate names, so you can quickly spot them visually. It does depends on there being no input errors, and it's not very elegant. The reference number you put beside each name will tell you which list it is on. It's not the way I'd do it myself, but it's the simplest one to explain here.

  • 1 month ago

    Using a formula to do as you wish would be fruitless. I infer by 'spreadsheets', you have 80 Excel workbooks (based on your 'What EXCEL formula' can I use).  If so, then to me, then I would merge all workbook worksheets into a single workbook.

    Then, I would write a VBA event handler to loop through each worksheet and compare each name against the names in each of the other worksheets.  

    Name matches would then be extracted to a 'Match' worksheet: Both Sheet Names and Row numbers, Employee Name, Email, Position, and Department. 

    The Match worksheet would display all duplicated entries for all 80 sheets.

    If you would like a no cost VBA routine tailored to your scenario, please let me know: excelhelp@comcast.net  

  • 1 month ago

    Merge them and then do a pivot table, That will let you show any duplications.

Still have questions? Get answers by asking now.