help: EXCEL: can 'VLOOKUP' find key words in a string

[sammx]

Hello geurs:
I wonder if anyone knows how to deal with this subject:
I got about 1000 orders with buyer's name and shipping addresses in csv file, which looks like:
order number: 123-456789-123
buyer name: John O.
shipping address: xxxxx

I also got tracking number for those orders in csv file, which looks like:
buyer name: John O.
shipping address: xxxxx
tracking number: 2329482903482

My question is: is it possible to use VLOOKUP to compile these 2 data set?
I need to associate the tracking number with order number by using relevant key words, such as buyer's name.
In excel, I know I can use 'find' function to look up every order...but it really takes time.
Many thanks,
sammx

 

[GlennUK]

Are you saying that any buyer will only have one order on file? That would have to be the case if you want to match using buyer's name.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[PeterMoran]

Hi,

VLOOKUP will perform a "like" in addition to an "=", meaning that you can use "*John*" to find the first buyer with the string "John" anywhere in the buyer name. "John O", "Bill Johnson", and "John Smith" would all satisfy this criteria.

This may get you started, rather than solving your problem.

Good Luck!

Peter Moran

 

[sammx]

Hi GlennUK,
Yes, any buyer will have exactly one tracking number. no duplicates.
do you know how to solve?
thanks

 

[KenWright]

As long as that name is unique (Not a good choice for an index field), then this should be easy, but first you need to normalise your data.

Assuming your data in the format as shown, and dataset1, ie

order number: 123-456789-123
buyer name: John O.
shipping address: xxxxx

is for example in the following ranges (I'm assuming a blank line betgween records, but no matter if not)

A1 order number: 123-456789-123
A2 buyer name: John O.
A3 shipping address: xxxxx
A4
A5 order number: 123-456789-123
A6 buyer name: John O.
A7 shipping address: xxxxx
A8
A9 order number: 123-456789-123
A10 buyer name: John O.
A11 shipping address: xxxxx
and so on...........

Copy column A and paste to Column B and to column C, so you end up with three identical columns.

Now select B1 and do Edit / Delete / 'Shift Cells UP'
Now select C1:C2 and do Edit / Delete / 'Shift Cells UP'

Select all of columns A:C and do Edit / Go To / Special / Blanks, and then do Edit / Delete / Entire Row.

Your data is now in a database format, so do the same to the second dataset, and then use VLOOKUP as normal to pull data from ds2 into ds1.

Regards
Ken...........




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------