Difference between 2 excel spreadsheet

P

Peter

Someone has given me two exported results from a query in
CSV format.

Is there any easy way in Excel to find out whether they
are exaactly the same (ie all rows and columns matched) ?

Thanks
 
G

Grizzly Bear

Here is one way (but, there's probably a better way):

Load both csv files into the same workbook either as seperate sheets o
on the same sheet. I would probably use seperate sheets so that I coul
reference any differences a little easier. (A1 on sheet 1 should = A
on sheet 2)

Then insert a "difference" sheet (SHEET3) and in A1 use this formula:

=IF(SHEET1!A1=SHEET2!A1,1,0)

Then select SHEET3 A1 and copy. Then select on SHEET3 all the cell
corrisponding to your data and paste.

Cells that are the same will be a 1 and any differences will be a 0.

To make it easier to see which cells are different, you can use
conditional format by selecting all the cells on sheet 3 and selec
format/conditional formatting from the menu. Make condition 1 = Cel
value is, equal, 0. Click format and then "patterns tab" and select RE
or some other highly visible color. Then OK and OK.

Presto: All the cells that are different will now be RED!

GrizzlyBea
 
A

Alan Beban

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=RowsEqual(rng11,rng2) will return TRUE if they are exactly the same,
FALSE if they are not.

Alan Beban
 
D

Dave Peterson

Maybe you could try using MSWord's comparison since the files are plain old text
files.
 
H

Harlan Grove

Alan Beban said:
If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=RowsEqual(rng11,rng2) will return TRUE if they are exactly the same, FALSE
if they are not. ....
Click to expand...

While the OP did ask for an Excel solution, there are times when there are
better tools for the task. Checking that two CSV files are identical could
be accomplished with the single console command

[C:\whatever] comp 1.csv 2.csv

As for Excel, if both were loaded in memory, why not use an array formula
like

=AND(1.csv!A1:BZ5000=2.csv!A1:BZ5000)

or, if they were defined names of possibly different size,

=ISNUMBER(1/AND(rng1=rng2))

?
 
Top