Matching records (Excel 2007)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gary - 15 Oct 2007 01:59 GMT

I have two text files:

FILE-1 has 2,500 rows, each cell contains a nine-digit number.
Examples:
391300004
453140065
672260027

FILE-2 has 1 million rows, each containing two or three nine-digit
numbers.
Examples:
391300004 391140021
391300004 391140021 391411001
391300004 391140021 391411002
453140065 453741001
453140065 453741001 460191001
453140065 453741002
453140065 453741002 460191002
453140065 453741003
672260014 672260046 672822058
672260014 672260046 672822059
672260017 672260041
672260017 672260041 673020006
672260017 672260042
672260017 672260042 672260047
672260027 672260046
672260027 672260046 672811001
672260027 672260046 672811002

I need to extract the row(s) in FILE-2 whose first nine-digit number
matches the nine-digit number in FILE-1.

Can someone provide step-by-step instructions on how I can do this?

Reply to this Message

OssieMac - 15 Oct 2007 06:05 GMT

Hi Gary,

Use VLOOKUP function. Insert the list of data to look for (File-1 2500 rows)
in one worksheet (Sheet1) and the list of data to lookin (FILE-2 1 million
rows) in another worksheet (Sheet2).

Insert column headers in row 1 of both sheets. (Col1, Col2 etc will do)

Insert the VLOOKUP formula in column 4 Sheet2 with the 1 million rows as per
the example:-

Insert formula in cell D2

=VLOOKUP(A2,Sheet1!$A$2:$A$4,1,FALSE)

Where A2 is the value in the first column of sheet 2

Sheet1!$A$2:$A$4 is the full range of data to be looked up in sheet 1 (Not
including the column headers). Note that $A$2:$A$4 is in absolute mode with
the $ signs.

Copy the formula to the bottom of the data in Sheet2.

Found data will cause the matching value to be placed in the cell in column
D and those not found will have #N/A.

I suggest that after doing this you select all of column D and
copy->paste special->values
so that as you do further manipulations with the data, it does not try to
recalculate and cause your system to halt with the amout of data you have.

If you need further help to extract the data after that then get back to me.

Regards,

OssieMac

> I have two text files:
>
[quoted text clipped - 29 lines]
>
> Can someone provide step-by-step instructions on how I can do this?

Reply to this Message

gary - 15 Oct 2007 19:30 GMT

Thanks!!!

What should the formula be if:

In my Sheet1, the data is in A2.A2926

In my Sheet2, the data is in A2.C1000000 (however, I want to find
matches between only the cells in Col A of Sheet2 and the cells in Col
A of Sheet1).

Gary

Reply to this Message

OssieMac - 15 Oct 2007 22:04 GMT

Hi Gary,

Insert this in cell D2 of Sheet2 and copy it down.

=VLOOKUP(A2,Sheet1!$A$2:$A$2926,1,FALSE)

Don't forget to select column D and then Copy->Paste Special->Values after
it has finished calculating otherwise you might have difficulties with it
recalculating when you try to manipulate the data.

Best way to delete the rows not required is to sort on column D. That will
put all the #N/A's together and all the matched records together. However, if
you have a need to return to the original order after the deletion then you
need to first create an additional column with an index so that you can then
re-sort on the index. To do this:-

Insert a column header called Index (or any name you like) in cell E1.
In cell E2 insert =ROW()
Copy this formula to the bottom
You will have an index created from the row numbers.
Select column E and Copy->Paste Special->Values. (Must be done)

You can then sort on column D, delete the block not required and then sort
on column E to get back to the original order.

Regards,

OssieMac

> Thanks!!!
>
[quoted text clipped - 7 lines]
>
> Gary

Reply to this Message

gary - 16 Oct 2007 02:15 GMT

Perfect! (Just what I needed).
And I leaned something new.

Thanks, Ossiemac

Reply to this Message

gary - 16 Oct 2007 07:33 GMT

Perfect! (Just what I needed).
And I learned something new.

Thanks, OssieMac!

Reply to this Message