Forum Discussion
Change the layout of my data
I suggest to add an additional sheet2 and enter the unique course descriptions in range A1:DJ1 (if there are 114 descriptions altogether).
Then you can enter below formula in A2 and fill over to DJ2:
=FILTER(sheet1!$A$3:$A$153;(sheet1!$C$3:$C$153=sheet2!A1)+(sheet1!$D$3:$D$153=sheet2!A1)+(sheet1!$E$3:$E$153=sheet2!A1)+(sheet1!$F$3:$F$153=sheet2!A1)+(sheet1!$G$3:$G$153=sheet2!A1))
Formula so far covers range for course1, course2 , course3, course4 and course5 which is column C, D, E, F and G in sheet1 but can easily be adapted to the remaining columns H to Q.
I suggest to add an additional sheet2 and enter the unique course descriptions in range A1:DJ1 (if there are 114 descriptions altogether).
Then you can enter below formula in A2 and fill over to DJ2:
=FILTER(sheet1!$A$3:$A$153;(sheet1!$C$3:$C$153=sheet2!A1)+(sheet1!$D$3:$D$153=sheet2!A1)+(sheet1!$E$3:$E$153=sheet2!A1)+(sheet1!$F$3:$F$153=sheet2!A1)+(sheet1!$G$3:$G$153=sheet2!A1))
Formula so far covers range for course1, course2 , course3, course4 and course5 which is column C, D, E, F and G in sheet1 but can easily be adapted to the remaining columns H to Q.
You are welcome to post replies if you have questions. I’m glad my suggestion helped.
OliverScheurich I have been playing with this today and I am struggling. I keep receiving the below error. Thoughts?
Most probably you shall use commas instead of semicolons and like. Usual issue if don't have Excel file with formula.
