Compare two lists

[Guest]

I’m comparing two lists in Excel 2002. Each list has 5000+ rows and 5
columns. Each record (row) has a unique ID number. So first I need to
compare the two columns that have that ID number and tag anything that’s on
one list and not on the other.

Next, I need to compare the other columns in the rows that correspond to
matching ID numbers and tag any data that is different.

I think I can manage to do one of these tasks, but I can’t do both. I’ve
tried sorting the lists by the ID number so the two lists will correspond,
but once I’ve found a record in one list that’s not on the other list, the
following records no longer line up and I can’t compare the data in the other
cells. The extra row (or missing row) can be on either of the lists.

I HOPE this is an obvious problem and someone can help me. Thanks for your
help!

 

[Bernard Liengme]

Visit www.cpearson.com where Chips has lots of stuff on duplicates
best wishes

 

[Mbt6]

I'm comparing two lists in Excel 2002. Each list has 5000+ rows and 5
columns. Each record (row) has a unique ID number. So first I need to
compare the two columns that have that ID number and tag anything that's
on
one list and not on the other.

Next, I need to compare the other columns in the rows that correspond to
matching ID numbers and tag any data that is different.

I think I can manage to do one of these tasks, but I can't do both. I've
tried sorting the lists by the ID number so the two lists will correspond,
but once I've found a record in one list that's not on the other list, the
following records no longer line up and I can't compare the data in the
other
cells. The extra row (or missing row) can be on either of the lists.

I HOPE this is an obvious problem and someone can help me. Thanks for
your
help!

Is it possible the item on list 1 needs to be compared against multiple
records in list 2 (more than one possible match)?

 

[Guest]

No, each list has unique ID numbers, so there won't be more than one of each
ID number on each of the two lists. My problem is that once I match up the
ID numbers on each list (and find the ones that don't have a match), I then
need to compare the rest of the row for those matcjomg records and make sure
the rows are identical. It's like I have a two-part problem, and I'm having
trouble managing both parts. Thanks for your interest.

 

[Pete_UK]

Assuming your ID numbers are in column A of both sheets, you can enter
this formula in cell G1 of sheet 1:

=VLOOKUP(A1,'Sheet 2'!$A$1:$A$5000,1,0)

and this one in G1 of sheet 2:

=VLOOKUP(A1,'Sheet 1'!$A$1:$A$5000,1,0)

If you don't have exactly 5000 records in either sheet, then adjust
accordingly. Copy the formula down column G to the bottom of your data
in each sheet. Any values in column A of one sheet which are not
present in column A of the other sheet will be flagged up with #N/A, so
these represent the unique IDs - you can filter column G for #N/A in
each sheet then copy the records to another sheet, as you don't need to
do any further checking. With the filter applied, you can use Edit |
Delete row to remove them from your lists after copying them elsewhere,
or you could sort the data to get them bunched together.

It is then only the remaining records which need to be checked against
each other - there should be the same number on both sheets, and if you
have sorted them they will occupy the same row on each sheet. Then in
cell H1 of sheet 1, you can have a formula like:

=IF(B1<> 'Sheet 2'!B1,"different","same")

This can be copied across 3 more columns, then can be copied down for
as much data as you have.

This will indicate where the two values of B, C, D and E are not the
same in duplicated records (you could filter them), and then you would
have to decide which one to change. Ultimately, if all the duplicated
records are identical, then you will only have "same" in these columns.
Then you could copy these records to the bottom of the unique ones
stored in the third sheet to give you your combined list.

Hope this helps.

Pete

 

[Max]

Here's one play to try ..

Sample construct available at:
http://www.savefile.com/files/6790732
Compare_Two_Lists_ClairView_gen.xls

Assuming the 2 source tables/lists are identically structured in sheets: A
and B, with data in cols A to E, from row2 down. [Key col = col A (IDs)]

In sheet: A

Put in F2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B!A:A,0)),"Y","N"))

Put in G2:
=IF($F2="Y",IF(INDEX(B!B:B,MATCH($A2,B!$A:$A,0))=B2,"Y","N"),"")

Copy G2 to J2
Select F2:J2, fill down

Col F returns the results of the check on the ID against the list in sheet:
B, cols G to J returns the corresponding results for the rest of the other 4
cols

And conversely, to check what's in B against what's in A ..

In sheet: B

Put in F2:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,A!A:A,0)),"Y","N"))

Put in G2:
=IF($F2="Y",IF(INDEX(A!B:B,MATCH($A2,A!$A:$A,0))=B2,"Y","N"),"")

Copy G2 to J2
Select F2:J2, fill down

--