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

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!

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sax30" wrote in message
...

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!


--
sax30

If you want to, the sample file: sax30_wksht.xls
is at: http://flypicture.com/p.cfm?id=39597
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks Max. I will try it out and post a result on Monday.

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sax30" wrote in message
...

Thanks Max. I will try it out and post a result on Monday.

An observation .. think from where you're reading this [
"excelbanter.com" ], the formula below has been slightly distorted. There's
a missing "less than" symbol just after the COUNTIF(...), before the "1".
Ensure that this missing symbol is inserted. Anyway, the sample file [link
posted earlier] contains the working implementation

Put in F2: =IF(C2="","",IF(COUNTIF(Sheet1!C:C,C2)1,ROW(),""))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi Max,
I really appreciate your help. It works like a charm and will save me a lot of times. Thanks again!

Great to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"sax30" wrote in message
...

Hi Max,
I really appreciate your help. It works like a charm and will save me a
lot of times. Thanks again!

"Max" wrote in message
...
"sax30" wrote in message
...

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:
....
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!

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