Vlookup & Find
 

I have a listing of good product numbers in Sheet 1 column A. I have
partial numbers and in some cases the complete product number in column
1 Sheet 2. I want to be able to retrieve, ie. find the match in column 1
sheet 1 given the fact that my product numbers may not be complete in my
second list Sheet 2 Column 1.


To explain the problem more clearly. I have a list of tool rental
equipment with their product ID's. They are of varying length. Some have
dashes slashes etc(eg. 28/2). I have the equipment which is used out in
the field and the product numbers in some cases have been worn off. I
might be missing the beginning end or middle of a number. I want to be
able to quickly match the partial number with my master list in Sheet 1.

Any help would be greatly appreciated as to how I might match up(best
efforts) Sheet2 Col 1 with Sheet 1(Master) Col. 1. There are literally
thousands of items and I'm going nuts trying to find the equipment in
the master List(Sheet 1)

Help

Regards

Ronald Cayne

Vlookup & Find
 

=match(A1,Sheet1!$A:$A,0)
=Match("*"&A1&"*",Sheet1!$A:$A,0)
=Match("*"&Text(A1,"#")&"*",Sheet1!$A:$A,0)
=Match("*"&A1&"*",Text(Sheet1!$A:$A,"#",0)
Put these in B1, C1, D1 and E1 of sheet2
The last one must be entered with Ctrl+Shift+enter rather than just enter
since it is an array formla

in F1 put in a formula

=if(iserror(B1),"",Index(Sheet1!$A:$A,B1,0))
then drag fill this to the right to I1

Then select B1:I1 and drag fill down the column

this won't find duplicate possible matches, but it should give the "90%"
solution
--
Regards,
Tom Ogilvy


"Ronald Cayne" wrote in message
...
I have a listing of good product numbers in Sheet 1 column A. I have
partial numbers and in some cases the complete product number in column
1 Sheet 2. I want to be able to retrieve, ie. find the match in column 1
sheet 1 given the fact that my product numbers may not be complete in my
second list Sheet 2 Column 1.


To explain the problem more clearly. I have a list of tool rental
equipment with their product ID's. They are of varying length. Some have
dashes slashes etc(eg. 28/2). I have the equipment which is used out in
the field and the product numbers in some cases have been worn off. I
might be missing the beginning end or middle of a number. I want to be
able to quickly match the partial number with my master list in Sheet 1.

Any help would be greatly appreciated as to how I might match up(best
efforts) Sheet2 Col 1 with Sheet 1(Master) Col. 1. There are literally
thousands of items and I'm going nuts trying to find the equipment in
the master List(Sheet 1)

Help

Regards

Ronald Cayne