Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find partial text and retrun value in corresponding column
Hi All
I have an inventory spreadsheet that has an A1 column approx 5000 rows long, that has part descriptions and a B1 column of part locations Approx 5000 rows long. I want to be able to enter a search criteria in a 3rd column say c1 and be able to look for partial matches in the description column. For Example if the Description says "Guide Roller shaft" I would like to be able to display the part location in the column next to it if I were to enter "Guide" or "Roller" or "Shaft" in the search criteria box SO if the columns were as follows and I entered "Roller" or "Guide" or "Shaft" in to C1 I would see A178 in D1 my results column. Also there may be several instances of the search criteria found within the description column. I would like to be able to see all locations returned in a list below the D1 column heading. A1 b1 C1 D1 Guide Roller Shaft A178 Roller A178 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find partial text and retrun value in corresponding column
hi,
Am Wed, 26 Dec 2012 10:57:49 -0800 (PST) schrieb : A1 b1 C1 D1 Guide Roller Shaft A178 Roller A178 try in D1: =INDEX($B$1:$B$100,MATCH("*"&C1&"*",$A$1:$A$100,0) ) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find partial text and retrun value in corresponding column
YEs that works great but it only lists the first instance offound of the search criteria. Can you show me how to make it find and list all that it finds?
On Wednesday, December 26, 2012 1:57:49 PM UTC-5, wrote: Hi All I have an inventory spreadsheet that has an A1 column approx 5000 rows long, that has part descriptions and a B1 column of part locations Approx 5000 rows long. I want to be able to enter a search criteria in a 3rd column say c1 and be able to look for partial matches in the description column. For Example if the Description says "Guide Roller shaft" I would like to be able to display the part location in the column next to it if I were to enter "Guide" or "Roller" or "Shaft" in the search criteria box SO if the columns were as follows and I entered "Roller" or "Guide" or "Shaft" in to C1 I would see A178 in D1 my results column. Also there may be several instances of the search criteria found within the description column. I would like to be able to see all locations returned in a list below the D1 column heading. A1 b1 C1 D1 Guide Roller Shaft A178 Roller A178 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find partial text and retrun value in corresponding column
On Wednesday, December 26, 2012 12:44:21 PM UTC-8, wrote:
YEs that works great but it only lists the first instance offound of the search criteria. Can you show me how to make it find and list all that it finds? Here's one way, using E as a helper column. In E1 put =IF(ISERROR(FIND($C$1,A1)),"",1) In E2 put =IF(ISERROR(FIND($C$1,A2)),"",MAX($E$1:E1)+1) and copy down past the end of the list. In D1 put =IF(ROW()MAX(E:E),"",INDEX(A:B,MATCH(ROW(),E:E,0) ,2)) and copy down past the end of the list. Hope this helps getting started. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
find partial text contained in another cell | Excel Discussion (Misc queries) | |||
Hide Column Based on Partial Text Match in Header | Excel Programming | |||
Getting only partial text sort in Column (2003 excel) | Excel Discussion (Misc queries) | |||
Make new column using partial text in another cell | Excel Worksheet Functions |