Compare, hightlight differences & merge

**creimke** · 04-06-2009, 08:28 AM

Hi all,

I'm experiencing problems getting my problem solved so i thought i'd ask you guys since you have loads of experience. I've already browsed the forum and found similar problems but haven't found a solution i can use yet.

What i'm trying to do is the folowing:

I have 2 excel 2007 documents with aproximately the same information, document 1 being the master and document 2 being the edited version. Ideally i would like an automated solution to compare the 2 documents and highlight the differences.

e.g. When changes are made in the edited document, when columns are added or deleted, ... i would like all differences to be highlighted (in the master and/or the edited document) to get a quick visual overview of what has been changed.

After the comparison of the 2 documents, i would like to merge the 2 documents into a third new document.

Is there a functionality or macro in excel 2007 to achieve this? If it isn't possible to do this with 2 seperate documents but can be done with 2 seperate sheets in the same document, i could also copy the contents of the second document into a new sheet in the master document.

I hope you can help me. Many thanks in advance.

Greetz,
Creimke

**broro183** · 04-06-2009, 11:50 AM

hi Creimke,

I only use Excel 2003 but it may be worth a try.

If the spreadsheets are small (are they?), the quickest way may be to manually...
copy both sheets (eg "Master" & "Edited") into the same file (purely for ease of use).
insert a new sheet & name it "Comparison".
in cell A1 of "Comparison" type the formula "=Master!A1=Edited!A1" & copy this formula down as many rows & across as many columns as needed.
Now do a visual search for "false" in each of the columns. Or you could apply an Autofilter to the Comparison sheet & then filter for "False" on each of the columns. You can then review the relevant cells on the other sheets...

If the files are large, I would be more likely to use a macro approach. However, more clarity is needed about what could be done in the "edited" version, because as soon as a row or column is inserted it becomes more (very?) difficult to correctly identify differences in subsequent cells (below or to the right of, resp.).

I strongly suggest that users are discouraged or prevented from inserting rows or columns in the edited version. If extra info/analysis is needed, it can be added to the far right or after the last row of the existing format in the Master file.

If comparisons are to be a regular occurence, I suggest using the "share workbook" option under the Tools menu in the Master workbook (+ save an unmodified copy just in case!) & distributing or providing network access to a single version of the file (ie "one version of the truth").
Personally, I always untick the "track changes" option on the Advanced tab of the "share workbook" option because use of this option can cause excessive file size increases & may make file corruption more likely.

hth
Rob

**creimke** · 04-07-2009, 04:07 AM

Hi Rob,

Thanks for your quick reply.

The files are quite large (over a thousand records already) and will continue to grow. So comparing them manually with the formula you suggested is not an option i'm afraid, not even with the autofilter option.

Your other suggestions are very useful though. I will rethink the setup of the files so columns can be added to the far right. I didn't think of that yet. The shared workbook option is also worth considering. I don't know that functionality yet but i'll look into it.

Thanks a lot for your help!

Greetz,
Creimke

**broro183** · 04-07-2009, 03:29 PM

hi Creimke,

Thanks for the feedback :-)

In my work I really push for "one version of the truth" so I'm usually for a single template file with a robust layout that it provides all the info that specific users want. Developing the completeness of info often involves quizzing the intended audience to find out their needs are, building a dummy & offering it for review before rolling out the finished file.

best of luck & post again if you have any more specific questions or want a macro developed...
Rob

**davbeisner** · 06-29-2010, 09:40 AM

I've got a similar problem where I have two mailing lists, each one nearly 4000 lines long, that need to be compared and merged. One is updated and includes more people, but the other includes opt-out preferences of the original batch of 3800+. The documents aren't even close to the same in terms of columns (one includes addresses and separated first/last names while the other has first/last names together and only e-mail addresses) and I'm at a loss as to how I can combine these two files... I don't think these suggestions will help me any. Do you have any other suggestions for me?

**broro183** · 06-30-2010, 04:36 PM

hi Davbeisner,

Welcome to the Forum :-)

This quite an old thread. Can you please re-read Rule #2 & start a new thread?

To help us help you, I suggest you also include a dummy sample of the two files in your new thread (Go Advanced - Manage Attachments or paperclip icon). Sample files will provide context for any helpers.

Thanks
Rob