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

Copper Contributor

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

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!!!

# Re: 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)),