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

[sax30]

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

Max Wrote:
> 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 atyahoodotcom
> ----
> "sax30" [email protected] wrote in message
> news:[email protected]
>
> 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-


--
sax30

[Max]

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"sax30" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Max. I will try it out and post a result on Monday.

[Max]

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 <at>yahoo<dot>com
----