Excel 2003 SP3 - Compare Excel files

Hi,

I have 2 Excel files, or DBF files, I'd like to compare in order to return all rows which are different.

Is there any tools in Excel to do that?

But also, if there is a new row in the new file, in the middle of the others, how such a tool would work? Because, it could potentially return all the following rows because row 73 in file 1 (old file), for example, would become row 74 in file 2 (new file). It can happen if there is a new row 72 in the new file version...

Any suggestion?

Hi,

Are you trying to compare every single field of a row record, or just one or two key fields.

The standard solution to this common request is to use a helper column in each of the two files and concatenate the values from the relevant fields into a single string, e.g.

Please Login or Register  to view this content.

Then use a VLOOKUP() from one file to the other making the subject (first term) of the VLOOKUP the helper column in one file, and the object being the table of values and column offset (2nd & 3rd terms) in the other file. And of course vice versa.
You also need to include FALSE as the 4th parameter if the tables are unsorted.

HTH

Thank you!

However, I cannot find out properly how to use the VLOOKUP function to compare files.

1. I merged the 2 workbook into a single Workbook with 2 Spreadsheet
2. I Concatenate the fields I'd like to campare into the first column of each Spreadsheet
3. Now, how can I use the VLOOKUP?

There is 3 cases that can happened (File 0 is the old version and File 1 the new version):
- File 1 has now records inserted anywhere. Is the VLOOKUP able to match similar return even if there are not on the same row anymore?
- File 1 has modified records
- File 0 has records which are deleted in the File 1

Not easy!

Perhaps, I had better to save my files as .txt and use a text comparaison tool?

Hi,

Assuming that column A in each sheet contains the concatenated strings, then just use the following in sheet 1

Please Login or Register  to view this content.

This will identify where records are the same in each sheet, or where there are either new records or records which differ ("No Match").

If there's still a problem, attach your workbook and I'll take a look.

HTH

Excellent!

It works perfectly, and I even adapted the formula to find out new records or just modified, even Deleted in the other file...

Cheers

Hi Richard,

A while ago, you helped me with the VLOOKUP function in Excel...

Today, I'd like to improve/modify it slightly. With the VLOOKUP, when there is a match, I'd like to copy & paste the matched cell (the value) into the cell where I applied my formula. Is that possible easily?

For example:

Columm A Columm B Columm C
Denver Alabama VLOOKUP ("Columm A"; "B:B"...) results to Colorado
Chicago Colorado

Cheers

Cheers[/QUOTE]

Hi,

If I understand your Q. correctly, just change it to:

Please Login or Register  to view this content.

HTH

Oups, sorry, my example was not exactly right!

Columm A ColummB ColummC ColummD
Denver Chicago Blue VLOOKUP(A3;B:B;1;FALSE) results to White
Chicago Denver White

In this case, I can match on the same line the info from ColummA and the info from ColummC... which is not exactly what I explained in my previous test.

Cheers