Oct 14 2021 02:11 PM
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 attached.
How do I do that?
Student ID | Course 1 | Course 2 | Course 3 |
2 | 2021 FA2 MBA 504 | 2021 FA2 MBA 562 | 2022 SP1 MBA 514 |
3 | 2021 FA1 BAN 500 | 2021 FA2 MIS 592 | 2022 SP1 BAN 501 |
4 | 2021 SU1 MBA 516 | 2021 SU2 MBA 504 | 2021 FA1 MBA 508 |
2021 FA1 MBA 508 | 2022 SP2 MIS 503 | 2023 SP1 MBA 532 | 2024 SP1 MBA 516 |
6 | 51 | 66 | 151 |
10 | 54 | 67 | 36 |
13 | 58 | 74 |
Any ideas are greatly appreciated,
Ashley Adams
Oct 14 2021 03:40 PM
SolutionI 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.
Oct 14 2021 10:48 PM
@yo_Ashley PowerQuery would be the easiest way out here. But, what you ask for will create a table with 45 rows and 218 columns, similar to @OliverScheurich 's formula based solution. Personally, I would prefer the table with the Course names down one column (i.e. 218 rows) and the 45 columns with student ID's. But, that's up to you, of course.
The attached file contains both options. The queries are static and run off the dataset in your file. For now, it will not work if you add students or courses to the data, but with a few extra steps it can be made dynamic. Just didn't go all the way. This is just an example of what's possible.
By the way, I deleted the hidden sheets from your original file. They included links to other workbooks that disturbed matters quite a bit when opening and saving the file.
Oct 20 2021 06:08 AM
Oct 20 2021 06:10 AM
Oct 20 2021 06:11 AM
Oct 20 2021 06:16 AM
Oct 20 2021 06:37 AM
@yo_Ashley Fair enough! But even when you will have 1000 students and 500 courses, I guess it's unlikely that any given course will have more than 500 students. In your own example with 152 student, the most popular course had 45 participants. That's why I proposed the alternative (vertical) lay-out. Changing column names takes a few seconds and making it fully dynamic, perhaps a few minutes.
Oct 20 2021 07:40 AM
Good point! It's safe to say @OliverScheurich had the best answer to my question, but @Riny_van_Eekelen your addition is the Best Upgrade.
Oct 20 2021 08:29 AM
You are welcome to post replies if you have questions. I’m glad my suggestion helped.
Oct 26 2021 07:57 AM
@OliverScheurich I have been playing with this today and I am struggling. I keep receiving the below error. Thoughts?
Oct 26 2021 08:24 AM
Most probably you shall use commas instead of semicolons and like. Usual issue if don't have Excel file with formula.
Nov 04 2021 07:24 AM
Nov 04 2021 10:12 AM
Use exact ranges, better tables or at least dynamic ranges
=FILTER(Students!A1:D10000,Students!F1:F10000="2022 SP1 MBA 504")
Nov 04 2021 10:22 AM
=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).
Oct 14 2021 03:40 PM
SolutionI 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.