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