Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Searching for partial data in a column
I am searching for partial data in column and want to return all possible
queries. Example: Search item in cell B1 Find all items on a second sheet in column a1 that contain b1 from original sheet. Not looking for exact matches, in other words a1 contains "exchangers" and on the other sheet column a1 might contain "heat exchangers" or just "exchangers" I want to return not only "heat exchangers" or "exchangers" but also the info in the rest of the row. A1 B1 1 Item Location 2 Exchanger Tank Farm 1 3 Heat Exchanger Tank Farm 2 Thanks for any help. If not clear ask and I will try and clearify. |
#2
|
|||
|
|||
Assumptions:
On Sheet2... A1:B10 contains your source data First row contains your headers/labels Formulas: On Sheet1... A1: enter your search term, such as 'Exchanger' B1: enter 0 (zero) B2, copied down: =IF((Sheet2!A2<"")*(ISNUMBER(SEARCH($A$1,Sheet2!A 2))),LOOKUP(9.999999999 9999E+307,$B$1:B1)+1,"") C1: =LOOKUP(9.9999999999999E+307,B:B) D2, copied down: =IF(ROWS(D$2:D2)<=$C$1,MATCH(ROWS(D$2:D2),$B$2:$B$ 10,0),"") E2, copied down and over to the Column F: =IF(N($D2),INDEX(Sheet2!A$2:A$10,$D2),"") Hope this helps! In article , "Severin" wrote: I am searching for partial data in column and want to return all possible queries. Example: Search item in cell B1 Find all items on a second sheet in column a1 that contain b1 from original sheet. Not looking for exact matches, in other words a1 contains "exchangers" and on the other sheet column a1 might contain "heat exchangers" or just "exchangers" I want to return not only "heat exchangers" or "exchangers" but also the info in the rest of the row. A1 B1 1 Item Location 2 Exchanger Tank Farm 1 3 Heat Exchanger Tank Farm 2 Thanks for any help. If not clear ask and I will try and clearify. |
#3
|
|||
|
|||
Have you thought about using Data|Filter|Autofilter.
Then you can use the arrow in column A's header to choose Custom|Contains exchanger If you need the data in another spot, you could copy and paste from there. Severin wrote: I am searching for partial data in column and want to return all possible queries. Example: Search item in cell B1 Find all items on a second sheet in column a1 that contain b1 from original sheet. Not looking for exact matches, in other words a1 contains "exchangers" and on the other sheet column a1 might contain "heat exchangers" or just "exchangers" I want to return not only "heat exchangers" or "exchangers" but also the info in the rest of the row. A1 B1 1 Item Location 2 Exchanger Tank Farm 1 3 Heat Exchanger Tank Farm 2 Thanks for any help. If not clear ask and I will try and clearify. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional format based on data from another column | Excel Discussion (Misc queries) | |||
2 Column Data lookup | Excel Discussion (Misc queries) | |||
Combining workbooks with some variable field names | Excel Discussion (Misc queries) | |||
Comparing Cells and Displaying Data | Excel Worksheet Functions | |||
filling a forumla down a column from data across a row | Excel Worksheet Functions |