Array formula? Select data from column B depending on adjacent value on Column A

[wanstronian]

Hi,

I have two columns of data, as below.

Col A	Col B
Fred	Wilma
Barney	Betty
Barney	Bam Bam
Fred	Pebbles
Fred	Dino

<tbody>
</tbody>

I want to create, using formulas instead of macros if possible, two further lists, with headings of "Fred" and "Barney" and under each, is the list of people/dinosaurs associated with each. So the two resultant lists would be:

Fred	Barney
Wilma	Betty
Pebbles	Bam Bam
Dino

<tbody>
</tbody>

So effectively I want to be able to select the list of things in column B, where the adjacent value in Column A is equal to the heading in my new list. It feels like an array formula might do this, but my initial attempts have been catastrophic failure.

Can anybody help?

Thanks
W

 

[Weazel]

maybe something like...

Excel 2012

	A	B
2	Fred	Wilma
3	Barney	Betty
4	Barney	Bam Bam
5	Fred	Pebbles
6	Fred	Dino
7
8	Fred	Barney
9	Wilma	Betty
10	Pebbles	Bam Bam
11	Dino
12
13

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula A9 {=IF(ROWS($A$9:A9)>COUNTIF($A$2:$A$6,A$8),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=A$8,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:A9))))} B9 {=IF(ROWS($A$9:B9)>COUNTIF($A$2:$A$6,B$8),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=B$8,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$9:B9))))} <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>