Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP to Find the MIN (that is not zero) | Excel Discussion (Misc queries) | |||
Vlookup to Find one value but exclude another | Excel Discussion (Misc queries) | |||
Vlookup to Find one value but exclude another | Excel Discussion (Misc queries) | |||
Can vlookup find the last row without it in the formula? | Excel Discussion (Misc queries) | |||
Vlookup & Find | Excel Worksheet Functions |