Forum Discussion

Ashley's avatar
Ashley
Iron Contributor
Oct 14, 2021
Solved

Change the layout of my data

I would like to change the layout of my data from the course data for each student ID showing up in rows to course data being listed at the top in column headers and student IDs below. Sample file at...
BI & Data Analysis
excel
  • OliverScheurich's avatar
    OliverScheurich
    Oct 14, 2021

    Ashley 

    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.

ncarabelli's avatar
ncarabelli
Copper Contributor
Nov 04, 2021
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")
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 04, 2021

ncarabelli 

=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).

Resources