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

[DeanPace]

I work at a university. I am pulling a system-generated excel report that looks like the image below:

System-generated report

I am needing to plugin formula(s) that will restructure this data to look like the cells between A10:I14 in the image below:

Desired Result

The problems I am facing:

1. I can have the first value to return correctly in Columns B (Fund Code 1) and C (Fund Amount 1). For Column B11 I just say [=if(b2>0,b1,if(c2>0,c1,if(d2>0,d1,if(e2>0,e1,""))))]. For C11 I say [=IF(B11=B1,B2,IF(B11=C1,C2,IF(B11=D1,D2,IF(B11=E1,E2))))]. These go down the row to look for the first cell with value. Where I am getting stuck is getting the second or third value to show up in Fund Code 2 and Fund Code 3 respectively. I need each student's second fund to appear in the same column even if the fund is not the same and not originally in the same column. This is also just a small subset of the report. I need to be able to do this on a large scale consisting of thousands of names and up to 70 different scholarships. Any help would be greatly appreciated!!!

[#2 · Accepted solution]

@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...

[#3]

Thanks so much, @djclements
Worked perfectly!