Combine Data

Tamra_Raleylostinexce In the second to last line (pvtData definition), where the EXPAND function is used, change the number of columns from 5 to 6. Also on the same line, change the wrap_count for the WRAPROWS function (the last argument at the far right) from maxCount*3 to maxCount*4:

=LET(
    head, A1:F1, data, A2:F1000,
    ...
    pvtData, HSTACK(students, WRAPROWS(TOCOL(DROP(SORT(VSTACK(fData, EXPAND(TOCOL(IFS(newRows>=SEQUENCE(, maxCount), keyId), 2),, 6, ""))),, 2)), maxCount*4)),
    ...
)

We could also add a few more variables to make the number of columns a little more dynamic...

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

keyCols is referring to the first 2 columns containing the student ID and Name, and pvtCols is the remaining number of columns to be transformed and output to a single row for each student. Adjust the head and data ranges to meet your needs.

Basically, a bunch of blank rows are being inserted into the data range for each student, based on the student with the most records. For example, student 18834 and 57111 both have the most records (7 each), so no blank rows are inserted for them. However, student 73214 only has 1 record, so 6 blank rows are added. Likewise, student 12345 has 2 records (5 blank rows added), and student 26997 has 3 records (4 blank rows added). This is to make sure each student contains the same number of records. TOCOL then sends the "padded" data to a single column and WRAPROWS transforms the data into the correct number of rows and columns. The VSTACK and HSTACK functions are used to add the headers and key columns (unique list of student IDs and Names), generating the final output.

If you have any more questions, please let me know.