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.
- Hi. We have been trying so hard to utilize your formula you posted so we can do multiple columns at a time but when we add in the plus signs we get a calc error. Below is the list of column formulas, any suggestions on how to combine them?
=FILTER(Students!A:D,Students!F:F="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!G:G="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!H:H="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!I:I="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!J:J="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!K:K="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!L:L="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!M:M="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!N:N="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!O:O="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!P:P="2022 SP1 MBA 504")
=FILTER(Students!A:D,Students!Q:Q="2022 SP1 MBA 504")=FILTER(Students!A:D,(Students!C:C="2022 SP1 MBA 504")+
(Students!D:D="2022 SP1 MBA 504")+
(Students!E:E="2022 SP1 MBA 504")+
(Students!F:F="2022 SP1 MBA 504")+
(Students!G:G="2022 SP1 MBA 504")+
(Students!H:H="2022 SP1 MBA 504")+
(Students!I:I="2022 SP1 MBA 504")+
(Students!J:J="2022 SP1 MBA 504")+
(Students!K:K="2022 SP1 MBA 504")+
(Students!L:L="2022 SP1 MBA 504")+
(Students!M:M="2022 SP1 MBA 504")+
(Students!N:N="2022 SP1 MBA 504")+
(Students!O:O="2022 SP1 MBA 504")+
(Students!P:P="2022 SP1 MBA 504")+
(Students!Q:Q="2022 SP1 MBA 504"))
Above formula works fine in my spreadsheet. If you only want the IDs to be returned, replace " Students!A:D " by " Students!A:A " .
=FILTER(Students!A:D,Students!G:G="2022 SP1 MBA 504")
This formula for example returns a calc error because course name 2022 SP1 MBA 504 doesn't exist in sheet Students in Column G (Course 5).
Use exact ranges, better tables or at least dynamic ranges
=FILTER(Students!A1:D10000,Students!F1:F10000="2022 SP1 MBA 504")
- The graduate school coordinator and I feel confident we can do this solution. May we post replies if we have questions?
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?
