Compare two spread sheets (1 Viewer)

[rhett7660]

Hello..

I did a search for compare and difference but I didn't find anything I was looking for. So I am asking..

Is there a way to compare two spread sheets without manually doing it? I want to see what is in one and not the other so I can base a report off of it.

IE

spread #1
a
b
c
d
e

Spread #2

a
b
c
d
e
f

I know spreadsheet number #2 has "f" but is there a formula or something I can use to tell me the difference? I am looking at say 600 items.

Thanks
R~

 

[Keith Nichols]

Clumsy way but might be enough

Rhet,

If you can copy both spreadsheets into a single book, it is pretty straightforward to do a comparison of one worksheet with another. The formula below in A1 of a third sheet will show if the cells A1 in the other 2 sheets have the same contents or the contents of both if they differ. This formula can be filled accross the necessary rows and columns to compare th entire sheets.

=IF(Sheet1!A1=Sheet2!A1,"Same",Sheet1!A1&" : "&Sheet2!A1)

If your spreadsheet has many columns as well as rows, you could use conditional formatting to highlight any different cells to be able to pick them quickly.

There may be a much cleverer way to do this but in a simple flatfile situation, this might be enough

Please find attached a quick example.

Edit,

In a moment of boredom, I have modified the spreadsheets to 600 rows by AD columns with meaningless data and made a couple of differences in one sheet. Viewing the analysis sheet at 25% allows you to easily spot the 2 cells that do not agree due to the conditional formatting.

Unfortunately, it turned out to be too lage to attach but it did work - honest!

Hope this helps.

 

[rhett7660]

Keith..

Thank you very much.. I am working on it as we speak. I will let you know how it works out!!

Thanks again
R~

 

[cherosoullis]

Not working for me

I have a similar problem with the difference that the data I want to compare are random. Firstly the solution for the problem mention above it can easily be solve with the "exact" fuction witch check two data and gives true of false. My problem is that my data are not straight forward. They are randomly with no sequential numbers. e.g.
1 2
4 4
5 3
6 6
7 8

I want to see if all numbers of column (b) are included in column (a).

 

[Smilie]

If you use a vlookup function you could see if there were any differences.

Put the formula in column C. When it comes back #N/A, that means that the column B # does not exist in column A. You could put in a reverse one to check the other way around if necessary.

=VLOOKUP(A:A,B:B,1,FALSE)

A B C
1 8 #N/A
2 5 2
3 2 3
4 3 4
5 4 5
6 9 #N/A
7 10 #N/A
8 21 8