Comparing two excel sheets

[leehale]

I have two sheets of data that I need to compare to make sure that the data is the same. Is there a function in Excel that will do this quickly for me.

Thanks
Lee

 

[xlbo]

vlookup

 

[leehale]

xlbo.
Thanks fo the response but how do I use a vlookup to check two sheets quickly? I am familiar with vlookups and cannot see how it will check a whole sheet of multiple columns that are updated daily and the differences are not easily visible and could be in any of the the columns anywhere inthe sheet.

When you are blind you need to be shown the way. Once.

Thanks in advance
Lee

 

[Madawc]

If the data is numeric, you could always do a 'hash total'. That's where you add up two lists of account numbers, say, which means nothing except that it checks that all the account numbers are there.

No doubt there is some much better way to do it in Excel, but I don't know it. In your place, I'd post the two sets column by column to a single sheet.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[leehale]

Thanks anyway but there is numeric as well as alphanumeric. My quest will carry on....

Thanks
Lee

 

[TonyJollans]

One way or another you are going to have to compare cell by cell. This, in a cell on another sheet, ...

[blue][tt]=SUMPRODUCT(--(Sheet1!1:100<>Sheet2!1:100))[/tt][/blue]

... will return a count of the differences between sheet1 and sheet2 (rows 1 to 100), so 0 if thry are the same.

No doubt ...

(a) you can adjust it for your own range, or a dynamic one, and
(b) there is a better way.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[leehale]

Thanhs Tony.
I will have a go and let you know.

Lee

 

[KenWright]

If the two sheets are IDENTICAL in structure, then why not simply have a 3rd sheet that references the top left of each table with something like

=if(Sheet1!A1=Sheet2!A1,"","CHECK")

Then just copy it across the relevant range, and you just look for anything not blank and use the cell reference as a pointer to where to look on the other sheets.

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

sorry for the brevity of the 1st post - assumed you were just trying to compare 1 column to 1 column