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.

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.

find multiple results
 

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.

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

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.

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.

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.

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

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.

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.