find multiple results

[Guest]

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.

 

[Guest]

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,

 

[Domenic]

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!

 

[Dave Peterson]

How about a User Defined Function:

http://groups.google.co.uk/group/mi...author:peterson&rnum=1&hl=en#db28f1ba868980a8

or

http://snipurl.com/i7q1

 

[Guest]

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

 

[Guest]

This formula gave me the exact response in the form I was looking for. Thanks

 

[Guest]

This response worked very easy and taught me some new things about excel.
Thanks

 

[Guest]

Thankyou for your assistance

How about a User Defined Function:

http://groups.google.co.uk/group/mi...author:peterson&rnum=1&hl=en#db28f1ba868980a8

or

http://snipurl.com/i7q1

 

[Guest]

Thank you for your assistance

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

 

[Guest]

Hi Domenic, Theres something wrong with the formula (logical test fault by
index) are you able to help ?

Re,
Frank

"Domenic" skrev: