Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple values in a list?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple values in a list?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple values in a list?
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple values in a list?
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if multiple values same/others different, return true | Excel Discussion (Misc queries) | |||
Help needed replacing multiple cells from a list of values. | Excel Discussion (Misc queries) | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
How to look up a value in a list and return multiple corresponding | Excel Discussion (Misc queries) | |||
Adding multiple cells, return specific values | Excel Worksheet Functions |