I have a spreadsheet that includes student name, parent name, class and t-shirt size, with one row for each student. I would like to draw from this to create a new table, where each class is a row, and the names of all of the students in the class and their emergency contacts are columns. I would also like another worksheet where the shirt sizes are rows, and each student is a column. My end goal is to use mail merge to generate a list of students for each teacher and a list of who should get each size t-shirt.
It seems like VLookup should be able to do it, or maybe there is a way with a pivot table.
Here is an example input
and outputs:
Can someone help me figure out how to get Excel to generate that information? Thanks in advance!
Best Answer
This Array Formula will solve the issue:
How it works:
Formula in Cell
G88
to get Table by T- Shirt:{=IFERROR(INDEX($A$88:$A$92,SMALL(IF($B$88:$B$92=$F88,ROW($A$88:$A$92)-MIN(ROW($A$88:$A$92))+1),COLUMNS($F$88:F88))),"")}
N.B.
Formula in Cell
G94
to get Table by Name & Contact Number:=IFERROR(INDEX($A$88:$A$92,SMALL(IF($C$88:$C$92=$F94,ROW($A$88:$A$92)-MIN(ROW($A$88:$A$92))+1),COLUMNS($F$94:F94)))," ")&" "& IFERROR(INDEX($D$88:$D$92,SMALL(IF($C$88:$C$92=$F94,ROW($D$88:$D$92)-MIN(ROW($D$88:$D$92))+1),COLUMNS($F$94:F94)))," ")
N.B.