Combine Data

Tamra_Raleylostinexce Based on the sample data provided, and if you're using Excel for MS365, the following dynamic array formula should do the trick:

=LET(
    head, A1:E1, data, A2:E1000,
    id, TAKE(data,, 1),
    fData, FILTER(data, id<>""),
    students, SORT(UNIQUE(TAKE(fData,, 2))),
    keyId, TAKE(students,, 1),
    recordCount, COUNTIF(id, keyId),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    pvtHead, HSTACK(TAKE(head,, 2), TOROW(IF(SEQUENCE(maxCount), DROP(head,, 2)))),
    pvtData, HSTACK(students, WRAPROWS(TOCOL(DROP(SORT(VSTACK(fData, EXPAND(TOCOL(IFS(newRows>=SEQUENCE(, maxCount), keyId), 2),, 5, ""))),, 2)), maxCount*3)),
    VSTACK(pvtHead, pvtData)
)

Please see the attached sample workbook...