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

[wanstronian]

Hi,

I have two columns of data, as below.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Wilma[/TD]
[/TR]
[TR]
[TD]Barney[/TD]
[TD]Betty[/TD]
[/TR]
[TR]
[TD]Barney[/TD]
[TD]Bam Bam[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Pebbles[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Dino[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Fred[/TD]
[TD]Barney[/TD]
[/TR]
[TR]
[TD]Wilma[/TD]
[TD]Betty[/TD]
[/TR]
[TR]
[TD]Pebbles[/TD]
[TD]Bam Bam[/TD]
[/TR]
[TR]
[TD]Dino[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Fred[/TD]
[TD]Wilma[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Barney[/TD]
[TD]Betty[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Barney[/TD]
[TD]Bam Bam[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Fred[/TD]
[TD]Pebbles[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Fred[/TD]
[TD]Dino[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFF00"]Fred[/TD]
[TD="bgcolor: #FFFF00"]Barney[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Wilma[/TD]
[TD]Betty[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Pebbles[/TD]
[TD]Bam Bam[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Dino[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A9[/TH]
[TD="align: left"]{=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))))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=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))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]