How to have Excel Formula(s) restructure data in a report

DeanPace What version of Excel are you using? If it's Excel for MS365, you could try the following single-cell dynamic array formula:

=LET(
    table, A1:E5,
    names, TAKE(DROP(table, 1),, 1),
    awards, DROP(TAKE(table, 1),, 1),
    values, DROP(table, 1, 1),
    rowId, SEQUENCE(ROWS(values)),
    test, NOT(ISBLANK(values)),
    unpivot, HSTACK(
        TOCOL(IFS(test, rowId), 2),
        TOCOL(IFS(test, awards), 2),
        TOCOL(values, 1)
    ),
    recordCount, BYROW(values, LAMBDA(r, COUNTA(r))),
    maxCount, MAX(recordCount),
    newRows, maxCount-recordCount,
    cols, SEQUENCE(, maxCount),
    resize, SORT(VSTACK(unpivot, EXPAND(TOCOL(IFS(newRows>=cols, rowId), 2),, 3, ""))),
    results, HSTACK(names, WRAPROWS(TOCOL(DROP(resize,, 1)), maxCount*2)),
    header, HSTACK("Student", TOROW({"Fund Code ";"Fund Amount "}&cols,, 1)),
    VSTACK(header, results)
)

Simply adjust the range reference for your actual table as needed. NOTE: I made the assumption that all blank cells shown in your sample screenshot are truly blank. If your actual data contains either zeros or zero-length strings for any unawarded scholarships, the formula will need to be modified accordingly.

Sample Results

Please download the attached sample workbook, if needed...