Seemed so simple :( Create a table based on values in rows

[roylionking]

Hi, been struggling with this, even into the rabbithole of pivot table 'measures'...
I've got a bunch of names in a column.

Against each, in the next column I've got a category I've given them.
The columns are fixed but I might add new names and change the assigned categories.
All? I want to be able to do is have a table with columns with the categories, and under each one a list of names assigned to it..

Like..

Apple	Green
Orange	Orange
Pear	Green
Apricot	Orange
Grape	Green

becoming

Green	Orange	Red...
Apple	Orange	etc
Pear	Apricot
Grape

Seems simple but I can't figure it out... Help please!

 

[etaf]

what version of excel do you have

with 365 version you can use
transpose
and
filter

in fact i think the filter() can be simplifed to just 1 formula rather than copy twice - i'll have a look

Book1
	A	B	C	D	E	F
1	Apple	Green			Green	Orange
2	Orange	Orange			Apple	Orange
3	Pear	Green			Pear	Apricot
4	Apricot	Orange			Grape
5	Grape	Green
Sheet1

Cell Formulas
Range	Formula
E1:F1	E1	=TRANSPOSE(UNIQUE(B1:B5,FALSE))
E2:E4,F2:F3	E2	=FILTER($A$1:$A$5,$B$1:$B$5=E1)
Dynamic array formulas.

 

[roylionking]

Daym!!!
A very elegant solution and tbh I'm wow'd.
Thank you!

=h:l|c:141414|ch:19|ff:hel|fz:15pt]Grape[/XD][XD=h:l|c:141414|ff:hel|fz:15pt]Green[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][/RANGE]

Cell Formulas
Range	Formula
E1:F1	E1	=TRANSPOSE(UNIQUE(B1:B5,FALSE))
E2:E4,F2:F3	E2	=FILTER($A$1:$A$5,$B$1:$B$5=E1)
Dynamic array formulas.

 

[etaf]

you are welcome