drop down value yields multiple values

[alohaitsjeff]

Hey everyone,

I've tried scouring how to do this and I'm either looking in the wrong place or don't know the proper formulas to search for. I'll try to describe what it is I'm trying to accomplish.


I have a list of customers and each customer has X number of computers on their network. What I want to do is create a drop down list of customer names, and from that selection display all of the computer names from their network (so the results would yield vertically).

Any help is greatly appreciated. Thanks!

Customer Name Drop Down List	Computer Name Result
Customer#1	Computer#1
	Computer#2
	Computer#3
	Computer#4

<tbody>
</tbody>

 

[XLearner]

Hi,

Assuming the following setup

	A	B	C	D	E
1	Customer	Computer		Customer	Customer C
2	Customer A	Computer A1
3	Customer A	Computer A2			Computer C1
4	Customer A	Computer A3			Computer C2
5	Customer A	Computer A4			Computer C4
6	Customer B	Computer B1
7	Customer B
8	Customer B	Computer B3
9	Customer B	Computer B4
10	Customer B	Computer B5
11	Customer B	Computer B6
12	Customer C	Computer C1
13	Customer C
14	Customer C	Computer C2
15	Customer C	Computer C4

<tbody>
</tbody>

where E1 is your drop down list of Customer names. In E3:

=IF(ROW(1:1) > COUNTIFS($A$2:$A$15,$E$1, $B$2:$B$15,"<>"&""),"",
INDEX($A$2:$B$15,MATCH(SMALL(IF(($A$2:$A$15=$E$1)*($B$2:$B$15<>""),ROW($A$2:$A$15)),ROW(1:1)),ROW($A$2:$A$15),0),2))

validate with Ctrl+Shift+Enter (array formula) and copy down as necessary

Regards
XLearner

 

[alohaitsjeff]

Thank you. That did work!

 

[XLearner]

You're welcome and thanks for posting back

Regards
XLearner

 

[alohaitsjeff]

Just in case anyone uses the above formula, the cells have to have a value or else some data will not yield. The cells will be blank or will not appear correctly unless the cells above do not have data.

 

[Mike Szczesny]

Can you use something like this? I used data validation for cell D2 to select customer #. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F
1	customer	computer		customer	computer	a
2	a	a1		c	c1	b
3	a	a2			c2	c
4	a	a3			c4
5	a	a4			c5
6	b
7	b	b2
8	b
9	b	b4
10	b	b5
11	c	c1
12	c	c2
13	c
14	c	c4
15	c	c5
Sheet1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($B$2:$B$15,SMALL(IF($A$2:$A$15=$D$2,IF($B$2:$B$15<>"",ROW($B$2:$B$15)-ROW($B$2)+1)),ROWS($E$2:E2)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.