return multiple values in a list?
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
KC Rippstein wrote:
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.
"jiwolf" wrote in message
...
autofilter will work, but i need it as a function. i need to return the
values to a sencond worksheet
"Teethless mama" wrote in
message ...
AutoFilter is a easy solution
"jiwolf" wrote:
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?
|