Matching Data

J

joey

I have a worksheet that has all account numbers and names - 15,000 of themand
another worksheet that has some of the same account number and names - about
8,000.

Is there a way in Excel to match records so that I can see the 7,000 records
that are not associated with the 2nd worksheet? Or, do I need to use Access.

Thanks
 
F

Farhad

Hi,

Try this:

big sheet (15000):
A B C
1 account# Name =VLOOKUP(A1,smallsheet!$A$1:$A$8000,1,false)

copy drag the formula to end of your data (15000) then you should have 7000
of "N/A" .

Thanks,
 
J

JLatham

Assume account numbers in column A of both sheets. Add a 'helper' column in
the first sheet with the long list. Put this formula into a cell in the
helper column in same row with first account number (assumed row 2)

=COUNTIF(Sheet2!A:A,A2)

Next choose that column and use Data | Filter and show rows with a zero as
the value in that column. Those left in view are the ones unmatched on
Sheet2 (where your short list is).
 
S

Scoops

I have a worksheet that has all account numbers and names - 15,000 of themand
another worksheet that has some of the same account number and names - about
8,000.

Is there a way in Excel to match records so that I can see the 7,000 records
that are not associated with the 2nd worksheet? Or, do I need to use Access.

Thanks
Click to expand...

Hi Joey

In Book2 try:

=IF(ISNA(MATCH(C5,[Book1]Sheet1!$A$1:$A$15000,0)),"Not found","Found")

Regards

Steve
 
R

richzimm

I have a worksheet that has all account numbers and names - 15,000 of themand
another worksheet that has some of the same account number and names - about
8,000.

Is there a way in Excel to match records so that I can see the 7,000 records
that are not associated with the 2nd worksheet? Or, do I need to use Access.

Thanks
Click to expand...

Use the vlookup function. The arguments are as follows:
vlookup(lookup value, table array, column index #, (range lookup))

Assume column A in sheet1 contains the 15000 account numbers beginning
in cell A1 and column B in sheet2 contains the 8000 beginning in cell
B1 account numbers, your function would look like this

vlookup(A1,'Sheet2!$B$1:$B$8000,1,0)

range lookup is 1 or 0 where 0 provides an exact match and 1 provides
an approximate match.

You could copy this down for the remaining 14999 cells

Any of the records in Sheet1 that are not in Sheet2 will come up as #N/
A

Some caveats:

if you have duplicate account numbers this will not work because the
function looks through the entire column and returns the first match.

if you have any trailing/leading spaces or zeros or any other small
discrepancies between the account numbers, the function will not find
a match.

This should work, let me know if it doesn't
 
J

joey

Thanks all of you! I'll give it a try this weekend!

Scoops said:
I have a worksheet that has all account numbers and names - 15,000 of themand
another worksheet that has some of the same account number and names - about
8,000.

Is there a way in Excel to match records so that I can see the 7,000 records
that are not associated with the 2nd worksheet? Or, do I need to use Access.

Thanks
Click to expand...

Hi Joey

In Book2 try:

=IF(ISNA(MATCH(C5,[Book1]Sheet1!$A$1:$A$15000,0)),"Not found","Found")

Regards

Steve
Click to expand...
 
Top