Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
I'm trying to set up a spreadsheet that will give me the multiple and
various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
Hi Larry,
The simplest answer would be to highlight column B and go to Data Filter AutoFilter. You could then select your tool from the drop down box. Would that work? Regards, -- Ken Hudson "Larry Banach" wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
How about a User Defined Function:
http://groups.google.co.uk/group/mic...28f1ba868980a8 or http://snipurl.com/i7q1 Larry Banach wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
How about a userform with listboxes.
check out http://www.geocities.com/excelmarksway for mutiple auto filter or send me workbook and maybe I can design a feature for you. - - Mark "Larry Banach" wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
This formula gave me the exact response in the form I was looking for. Thanks
"Domenic" wrote: Assumptions: A2:A8 contains the part B2:B8 contains the tool D2 contains the criterion or tool of interest, such as 56789 Formula: E2, copied down: =IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$8,$D$2),INDEX(A$ 2:A$8,SMALL(IF($B$2:$B$ 8=$D$2,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS(E$2:E2))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Larry Banach <Larry wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
This response worked very easy and taught me some new things about excel.
Thanks "Ken Hudson" wrote: Hi Larry, The simplest answer would be to highlight column B and go to Data Filter AutoFilter. You could then select your tool from the drop down box. Would that work? Regards, -- Ken Hudson "Larry Banach" wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
Thankyou for your assistance
"Dave Peterson" wrote: How about a User Defined Function: http://groups.google.co.uk/group/mic...28f1ba868980a8 or http://snipurl.com/i7q1 Larry Banach wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
Thank you for your assistance
"exceluserforeman" wrote: How about a userform with listboxes. check out http://www.geocities.com/excelmarksway for mutiple auto filter or send me workbook and maybe I can design a feature for you. - - Mark "Larry Banach" wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
find multiple results
Hi Domenic, Theres something wrong with the formula (logical test fault by
index) are you able to help ? Re, Frank "Domenic" skrev: Assumptions: A2:A8 contains the part B2:B8 contains the tool D2 contains the criterion or tool of interest, such as 56789 Formula: E2, copied down: =IF(ROWS(E$2:E2)<=COUNTIF($B$2:$B$8,$D$2),INDEX(A$ 2:A$8,SMALL(IF($B$2:$B$ 8=$D$2,ROW($B$2:$B$8)-ROW($B$2)+1),ROWS(E$2:E2))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Larry Banach <Larry wrote: I'm trying to set up a spreadsheet that will give me the multiple and various results from a column in a spreadsheet. Example spreadsheet: col a col b Part Tool 12345 12345 23456 23456 34567 34567 45678 34567 56789 56789 67890 56789 78901 56789 if I was to search for all parts made from tool 12345 the result should be only part 12345, but if I searched for parts made from tool 56789, the results I'm looking to see are 56789, 67890, and 78901. I was able using index and match funtions to get a single answer but I would like to be able to display all associated results on the single worksheet. Maybe it's some kind of combination of if statements and index and match functions or find, I'm not sure. My spreadsheet has approximately 1000 part numbers made from several hundred different tools. Thanks in advance for the assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find multiple cells/replace whole cells w/data | Excel Discussion (Misc queries) | |||
Searching for multiple results in one cell | Excel Worksheet Functions | |||
how do you find the SD(standard deviation ) for the multiple reg | Excel Worksheet Functions | |||
Return Multiple Results with Lookup | Excel Worksheet Functions | |||
Displaying the results of multiple formulas in a single cell. | New Users to Excel |