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!
-
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.– NateAug 14, 2014 at 15:03
-
It might help to know what version of Excel you are using.– G-Man Says 'Reinstate Monica'Aug 14, 2014 at 23:19
1 Answer
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.