Merging/Comparing Data Between Different Excel Files

[Pavilion]

How many of you regular Excel users 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 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.

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=89267

 

[Isaac]

How many of you regular Excel users 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 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.

One of the biggest losses in recent years is the Shared Workbook over a network. I made great use of that back in its day.

But definitely, when you run into users with their own conflicting crap everywhere - that's when you sell the database idea!

 

[Pavilion]

One of the biggest losses in recent years is the Shared Workbook over a network. I made great use of that back in its day.

But definitely, when you run into users with their own conflicting crap everywhere - that's when you sell the database idea!

RE: the need to move to a database when there is conflicting files all over the place.

You and I agree completely. When they showed me the folder with all those spreadsheet files, the first thought in my mind was "Why?".

 

[Isaac]

RE: the need to move to a database when there is conflicting files all over the place.

You and I agree completely. When they showed me the folder with all those spreadsheet files, the first thought in my mind was "Why?".

After sitting for a few days trying to build requirements, you may put your head in your hands and cry, "why me?"
LOL - just kidding.

Great project opportunity and I hope it goes well for you. I miss my days of designing Access FE's very much sometimes, but enjoy the present as well.