Lookup/Search list

[lvboev]

Hi,
I'm hoping that someone will be able to help me. I have two excel files, both with a list of customers as well as extra information about each customer. One excel file has a shortish list (600) and the other has almost 40,000. I want to search the really long list of customers using the 600 short list. Essentialy I want to pull out from the long list any customers that fall into both spreadsheets.

I know this can be done as one of my predessesors at work had done it before but has now left and has taken the file with them.

Thanking anyone in advance

Lili

[Bryan Hessey]

Hi,
I'm hoping that someone will be able to help me. I have two excel files, both with a list of customers as well as extra information about each customer. One excel file has a shortish list (600) and the other has almost 40,000. I want to search the really long list of customers using the 600 short list. Essentialy I want to pull out from the long list any customers that fall into both spreadsheets.

I know this can be done as one of my predessesors at work had done it before but has now left and has taken the file with them.

Thanking anyone in advance

Lili

Assuming customer number/name/identifier is on Sheet1 in column A of both workbooks and the 600 customer filename is Short.xls and you have the workbook open:

In a blank column of your 40,000 customer worksheet, in row 1, put:

=VLOOKUP(A1,[Short1.xls]Sheet1!A:A,1,FALSE)

and formula-fill that down your 40,000 customers

Your list should fill mostly as #N/A but with 600+/- cutomers.

Select that column, then Data, Filter, Advanced filter and set Unique Records only. this will display 1 #N/A, (ignore that) and the remainder is your list.

hth
---

[lvboev]

Thankyou for that it does almost what i want it to do.

you see the thing is the customers are companies like "Royal Bank of Scotland" where they may have PLC or Group or various versions of the name at the end and I need it to find all the versions. Is there any way of making the search string only select say the first 5 letters? or maybe even changing the values in column A on the Short file so they only show the first 5 letters?

Lili

[Bryan Hessey]

You can, but be aware, that in 5 characters your lookup will match Smith to Smith Smithers Smithson etc.

Safer is, perhaps to run two columns of VLookup, the first to column A as a full match, the second, which you can use where no full match exists, can be (say) a 10 character match by using (you can vary this to your requirements)
=VLOOKUP(Left(A1,10),[Short1.xls]Sheet1!B:B,1,FALSE)
against a B column generated from =Left(A1,10) on the ShortList names.

hth
---

Thankyou for that it does almost what i want it to do.

you see the thing is the customers are companies like "Royal Bank of Scotland" where they may have PLC or Group or various versions of the name at the end and I need it to find all the versions. Is there any way of making the search string only select say the first 5 letters? or maybe even changing the values in column A on the Short file so they only show the first 5 letters?

Lili

[lvboev]

Bryan,

Thats perfect, thanks a lot for your help. You're a star!

Lili