return multiple values in a list?

[jiwolf]

I have a list that has two columns.. In column A is a list of containers. in
column B is a list of components.

The containers in A can have multiple components in them i.e.

Cont Comp

1 xyz
1 ABC
1 DEF
2 aaa
3 bbb
3 baa


how can I get a list of every component that appears in a given container?
Vlookup returns a component in a given box, but not EVERY component?


ideas anyone?

 

[jiwolf]

autofilter will work, but i need it as a function. i need to return the
values to a sencond worksheet

 

[KC Rippstein]

Why not just use a pivot table? It can give you an answer like this (you
would probably disable subtotals):
1
xyz
ABC
DEF
2
aaa

Or, if you need the results to fit into a template you've made, then you
could just put the components as headers across the pivot table and the data
will be a count of how many times a component shows up in each container
(again, disable subtotals). Off to the right of the pivot table, make a
string of all the headers that have a 1 for that row. Then you can use
vlookup and get the data you need from that string you formed.

Sorry, I don't know how to write functions very well, but I'm comfortable
with formulas and this would be a cinch to accomplish.

 

[vezerid]

Hi:
Assuming data are in A2:B8. Assuming lookup value in C2, your list
starts at D2. In D2:

=IF(ROW()-ROW($D$2)+1<=COUNTIF($A$2:$A$8,$C$2),INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$C$2,ROW($B$2:$B$8)-ROW($B$2)+1),ROW()-ROW($D$2)+1)),"")

This is an array formula, thus you have to commit with Ctrl+Shift+Enter

HTH
Kostis Vezerides