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:

A B 1 STUDENT FORM 2 John Y01A 3 Paul Y02C 4 Mary Y01B 5 June Y01A 6 Mick Y02C 7 Brian Y01B 8 Karen Y02C 9 Don Y01B 10 Pat Y01B <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The resultant data needs to be listed as below:

		C	D	E
1		Y01A	Y01B	Y02C
2		John	Mary	Paul
3		June	Brian	Mick
4			Don	Karen
5			Pat
6
7
8
9
10

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[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.