Question about Merging/Comparing data between Excel Files

[PavilionWI]

Hello Everyone:

How many of you have run into problems merging and comparing data between shared Excel workbooks?


I didn't know this was a thing. During a recent TEAM meeting, one of my MS Access clients asked me how to compare and merge different versions of an Excel spreadsheet. Then they opened one of their file drives to show me multiple versions of the same Excel file. There was a master file, but also several versions of the same master file. All of them were in the same folder.


As my client walked me through the situation, the following became obvious:


* Even though there was a Master file – all users had their own version.
* The person in charge of the Master file had tried merging data from user specific copies with Excel merge tools.
* However, during the process of merging changes, some of the Excel formulas in the Master file were broken and no longer worked.
* In addition – changes to the same rows of data by different users were not always merged accurately. This caused the team, as a whole, to distrust the data.


How common are these issues in Excel. I specialize in MS Access. Of course, many of my clients push data back and forth between Excel and Access. So there are times that I end up helping clients with Excel data. But - I've never had a client present this problem to me. And I'm just wondering how common it is????


I'd be interested in hearing from any of you about your first hand experiences with comparing/merging data between two Excel files.

[Welshgasman]

Crossposted https://www.access-programmers.co.uk...-files.330028/

[CJ_London]

any merging, be it excel or access needs to be done with care and with clear rules around what should be merged and how duplicates/bad data/etc should be treated. Also advisable to ensure you have a backup before merging. Sound like in this case, sufficient care wasn't taken.

Many years ago a utility company client asked me to merge some 5000 excel spreadsheets that had been created over a number of years. During that time, the layout had changed - additional rows and columns added, some removed. I wrote a vba routine to analyse each file to determine exactly where the required data could be found and saved the file with a modified file name in a separate folder. The file name showed when it was created, the 'layout version', the origin of the file among other things.

I was then able to write a routine to open each file in turn and based on the layout version, do a data validation and import to access (back then there was the 65k excel rows limit, the 64bit version did not exist). The rule we had was if the file failed on data validation, it was rejected and put into a 'review' folder for someone to check.

It took several days to determine all the different layout versions (there were around 80 if I recall correctly), a couple of hours to run the import and there were a couple of hundred files rejected for review.

Another client wanted an excel spreadsheet to be used as an import form (around 600 people, worldwide completed these forms) because everyone had excel, not everyone had access. We sent out a highly protected macro workbook which did data validation at the time of input. Access then parsed a specific email folder on a regular basis for completed forms, imported the data and the created an exported csv for import into a corporate system

[alansidman]

I have found the the easiest way to merge (join) excel files is with Power Query. It is quick and easy and can be done in the UI.