Creating Rosters/registers

[Carvell]

Hello,
I have a table which tells me which class a student is in

Student	Group1	Group2	Group3	Group4
StudentA	2	3	4	6
StudentB	3	4	1	7
StudentC	6	5	4	2
StudentD	2	3	7	1

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

I would like to change this into a list of students in each class

Class	1	2	3	4	5	6	7
Student

<colgroup><col width="64" style="width:48pt" span="8"> </colgroup><tbody>
</tbody>

Below each number I would like a list of the students in that class

So below class 1 it would say StudentA and then the row below would say StudentD

I can create the lists using COUNTIF but I end up with blank rows (which I don't want)

Is there a formula, that can make this from the table above?

Class	1	2	3	4	5	6	7
Student	StudentB	StudentA	StudentA	StudentA	StudentC	StudentA	StudentD
	StudentD	StudentC	StudentB	StudentB		StudentC
		StudentD	StudentD	StudentC

<colgroup><col width="64" style="width:48pt" span="8"> </colgroup><tbody>
</tbody>

Thank you

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	J	K	L	M	N	O	P	Q
1	Student	Group1	Group2	Group3	Group4		Class	1	2	3	4	5	6	7
2	StudentA	2	3	4	6		Student	StudentB	StudentA	StudentA	StudentA	StudentC	StudentA	StudentB
3	StudentB	3	4	1	7			StudentD	StudentC	StudentB	StudentB		StudentC	StudentD
4	StudentC	6	5	4	2				StudentD	StudentD	StudentC
5	StudentD	2	3	7	1
Sheet2

Cell Formulas
Range	Formula
K2	=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/($B$2:$E$5=K$1),ROWS($A$1:$A1))),"")

 

[Carvell]

That works perfectly, thank you.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Carvell]

That’s okay.

Is it possible to explain how it’s working.

Also, I’m pretty sure this is moving into the world of VBA (which I have no understanding). Is it possible from my original table to make a new worksheet for each unique class name and then add the student list?

Thanks

 

[Fluff]

This part of the formula
(ROW($A$2:$A$5)-ROW($A$2)+1)
returns an array of of numbers {1;2;3;4} representing the rows so A2 is the 1st row A3 the 2nd etc
This part
($B$2:$E$5=K$1)
returns an array of TRUE or FALSE depending on whether a cell matches the criteria in K1
The array of rows is then divided by the array of true/false which gives either a Div0 error or the row number.
This parrt
ROWS($A$1:$A1)
returns the number of rows, so in K2 it returns 1, in K3 it returns 2etc
This
AGGREGATE(15,6,...
Is the same as the SMALL function except it ignores errors, so in K2 it will return the smallest non error value.

If you select the cell with the formula & click "Evaluate Formula" on the Formulas tab, you can see it working each part out.

You will need to start a new thread for your other question.