MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing worksheets


Posted by Jason on January 08, 2002 3:26 PM

I have discovered that a large d-base I have been working on for the past 4 months is not accurate and need to compare cells from one worksheet with the cells from another worksheet. Somehow, probably something I did, my cells wound up out of order and I have to get them back in the proper order.

The situation:

I have two columns containing a client number in one and the client name in the other. While doing a random check today, I discovered that some of the client numbers did not match the client names. I need to get them matching again, to ensure that I my data is accurate.

Is there a way to compare these cells on one worksheet with the cells from another sheet? I have the correct order of the two columns on another worksheet, but feel that I need to compare the two and get them to match. Since I am self-taught, I thought I would turn to you all for help.

Thank you,
Jason
(If I have not explained this well, let me know and I'll try again....I'm brain dead, now!)


Posted by Derek on January 09, 2002 1:52 AM

This is one way to do it:

Copy your accurate list (in black font) to columns A and B of a new worksheet (A for client number, B for name).
Copy your doubtful list and paste it below BUT in red font. Now select the complete list and sort by column A. This will put the client numbers together for comparison. In C1 type this formula and scroll it down:
=IF(AND(A1=A2,B1<>B2),"X","")
This will place an "X" alongside those that do not match. Since you know the black font entry is correct then the red entry is the wrong one.
Hope this helps
Derek