Listing names from a column into new columns under their relevant heading

[Mazbuka]

I have a big list of students in Col A & their school form in Col B. I list each unique form as column headers from C onwards and underneath each header I want the list of students in that form.

My data sample looks like this:

[TABLE="width: 191"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 232"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]STUDENT[/TD]
[TD]FORM[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Y01A[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]Y02C[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]Y01B[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]June[/TD]
[TD]Y01A[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]Mick[/TD]
[TD]Y02C[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]Brian[/TD]
[TD]Y01B[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]Karen[/TD]
[TD]Y02C[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]Don[/TD]
[TD]Y01B[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]Pat[/TD]
[TD]Y01B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The resultant data needs to be listed as below:

[TABLE="width: 294"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Y01A[/TD]
[TD]Y01B[/TD]
[TD]Y02C[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]John[/TD]
[TD]Mary[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]June[/TD]
[TD]Brian[/TD]
[TD]Mick[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Don[/TD]
[TD]Karen[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Pat[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1	STUDENT	FORM	Y01A	Y01B	Y02C
2	John	Y01A	John	Mary	Paul
3	Paul	Y02C	June	Brian	Mick
4	Mary	Y01B		Don	Karen
5	June	Y01A		Pat
6	Mick	Y02C
7	Brian	Y01B
8	Karen	Y02C
9	Don	Y01B
10	Pat	Y01B
11
12
Main

Cell Formulas
Range	Formula
C2:E6	C2	=IFERROR(INDEX($A$2:$A$10,AGGREGATE(15,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($B$2:$B$10=C$1),ROWS($A$2:$A2))),"")

 

[Peter_SSs]

Edit: Looks like I was a fraction slow.

Try this copied across and down.

Excel Workbook
	A	B	C	D	E
1	STUDENT	FORM	Y01A	Y01B	Y02C
2	John	Y01A	John	Mary	Paul
3	Paul	Y02C	June	Brian	Mick
4	Mary	Y01B		Don	Karen
5	June	Y01A		Pat
6	Mick	Y02C
7	Brian	Y01B
8	Karen	Y02C
9	Don	Y01B
10	Pat	Y01B
11
List

 

[Mazbuka]

Magnificent...thanks both!

 

[Peter_SSs]

Magnificent...thanks both!

You're welcome. At least you got confirmation of the approach.