Comparison / Check Off between 2 excel sheets. HELP NEEDED!

[sax30]

Hi,

Let's say that I have two excel tables and both have the same field
names which contains data like Name, Social Security Number,
Address....and so far. For example:

Table #1

LastName FirstName SSN Address
PhoneNumber
Smith John 111-11-1111 123 Usa St.
123-458-9999
Smith Tim 222-22-2222 321 Main St.
123-444-5555

Table #2

LastName FirstName SSN Address
PhoneNumber
Smith John 111-11-1111 123 Usa St.
123-458-9999
Smith Tim 222-22-2222 321 Main St.
123-444-5555
Brunell Mark 333-33-3333 444 Usa St.
123-555-9999

Noticed that table #2 has an extra person. Otherwise they are both the
same. Now, Is there a way that I can do to make excel to point out that
333-33-3333 does not match any number in the table #1? If yes, please
show me the steps to get it done. The above is just a simple example.
My tables has hundred of names and it will take forever to check off one
by one manually on both table in order to see which table has a data
that does not match. Thanks in advance!

 

[Max]

Here's one crack at this ..

Assume Table #1 is in Sheet1, cols A to E, data from row2 down
where the key col, SSN is col C

Table #2 is assumed similarly set-up in Sheet2

In Sheet2
--------
Use an empty col to the right, say col F?
Put in F2: =IF(C2="","",IF(COUNTIF(Sheet1!C:C,C2)<1,ROW(),""))
Copy F2 down as many rows as there is data in Table #2

In a new Sheet3
-----------
Copy > Paste the same col headers into A1:E1

Put in A2:
=IF(ISERROR(SMALL(Sheet2!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet2!A:A,MATCH(SMA
LL(Sheet2!$F:$F,ROWS($A$1:A1)),Sheet2!$F:$F,0)))

Copy across to E2, fill down by as many rows as was done in Sheet2

Sheet3 will return all the rows from Sheet2's Table #2
whose SSNs do not match those in Table #1, bunched at the top

For the sample data above, you'll get:

Brunell Mark 333-33-3333 444 Usa St. 123-555-9999
(rest are blank rows)

Adapt to suit

 

[Max]

If you want to, the sample file: sax30_wksht.xls
is at: http://flypicture.com/p.cfm?id=39597

 

[Markus L]

Take a look at this product called "Excel Compare":
http://www.formulasoft.com/xlsc.html