Generate lists from array

[schris]

Hi, new user here who's not bad at Excel, but doesn't have a lot of experience with arrays. My problem:

Given a list of groups (in this example types of fruit) and membership in each group (names), I'd like to generate a rows where each person's group memberships are listed.

	A	B	C	D	E
1		Name1	Name2	Name3	Name4
2	Apple	Paul
3	Banana	Greg	George	Jeff	Ed
4	Orange	Ron	Paul	Greg
5	Grape	Chris	Ed	Paul
6
7	Output:
8	Chris	Grape
9	Ed	Banana	Grape
10	George	Banana
11	Greg	Banana	Orange
12	Paul	Apple	Orange	Grape
13	Ron	Orange
14	Jeff	Banana

<tbody>
</tbody>

I've tried variations of array, INDEX, MATCH and SMALL, but have had no luck. I'm starting to wonder if this is even possible with Excel.

Frustrated fruit vendor,
Steve

 

[Eric W]

Welcome to the Board.

Try:

	A	B	C	D	E
1		Name1	Name2	Name3	Name4
2	Apple	Paul
3	Banana	Greg	George	Jeff	Ed
4	Orange	Ron	Paul	Greg
5	Grape	Chris	Ed	Paul
6
7	Output:
8	Chris	Grape
9	Ed	Banana	Grape
10	George	Banana
11	Greg	Banana	Orange
12	Paul	Apple	Orange	Grape
13	Ron	Orange
14	Jeff	Banana

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula B8 {=IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$E$5=$A8,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($B8:B8))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[schris]

Holy cow Eric, it works! Thank you so much, I can't believe how fast you whipped that up, I've been struggling with it for hours.

Very much appreciated!
Steve

 

[Marcelo Branco]

If you do need/want to extract the names in alphabetical order maybe something like this

	A​	B​	C​	D​	E​
1​		Name1​	Name2​	Name3​	Name4​
2​	Apple​	Paul​
3​	Banana​	Greg​	George​	Jeff​	Ed​
4​	Orange​	Ron​	Paul​	Greg​
5​	Grape​	Chris​	Ed​	Paul​
6​
7​	Output:​
8​	Chris​	Grape​
9​	Ed​	Banana​	Grape​
10​	George​	Banana​
11​	Greg​	Banana​	Orange​
12​	Jeff​	Banana​
13​	Paul​	Apple​	Orange​	Grape​
14​	Ron​	Orange​
15​

<tbody>
</tbody>

Array formula in A8 copied down (gray area)
=IFERROR(INDIRECT(TEXT(SMALL(IFERROR(IF(IF(B$2:E$5<>"",COUNTIF(B$2:E$5,"<"&B$2:E$5)+1,"")-SUM(COUNTIF(A$7:A7,B$2:E$5))=1,ROW(B$2:E$5)*10^5+COLUMN(B$2:E$5)),""),1),"L0C00000"),0),"")
Ctrl+Shift+Enter

M.

 

[Eric W]

Well, it really helps if you've seen something like it before!

Glad we could help!