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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ClaireView" <froufle@notthishotmail.com> wrote in message
news:A7C2D5BB-A6A9-4347-B409-5CFABB8FA68F@microsoft.com...
> 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.
>
> "Mbt6" wrote:
> >
> > Is it possible the item on list 1 needs to be compared against multiple
> > records in list 2 (more than one possible match)?
> >
> >
> >
> > "ClaireView" <froufle@notthishotmail.com> wrote in message
> > news:13A02D05-775D-4A5B-BAAB-3A4F1BDB9941@microsoft.com...
> > > 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!
> > >
>