0

I have multiple (~20) Excel files and I want to check if there is duplicate data (e.g. name). Is there a way I can do this without going through every name of each file? Thanks!

2
  • The only way I can think about going about it would involve a VBA script (which I haven't worked out yet). Are you familiar/comfortable with VBA or macros? You could use a whole bunch of formulas as per this link, but that would get pretty unwieldy with 20 different workbooks.
    – Nate
    Aug 14, 2014 at 15:03
  • It might help to know what version of Excel you are using. Aug 14, 2014 at 23:19

1 Answer 1

0

I would use the Power Query Add-In for this. You can start a Query from a File Folder - it will import all the files in a Windows Folder and can append all their data together (assuming the file columns are consistent). It takes a few steps and a bit of fiddling in the M Scripting language, but the building blocks and further data manipulation is point-and-click

Here's a blog post that details the steps to combine Excel files:

http://dutchdatadude.com/combining-excel-files-using-power-query-for-excel/

To find the duplicates, I would duplicate the Query and use the Group By command in Power Query. I would Group By Name and calculate Count. Any row with Count > 1 is a duplicate. Then you can return to the first Query (with all the detail rows) and Merge the 2nd Query that finds duplicates, joining on Name.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .