Transpose uneven data sets

spudulene

New member
Joined
Aug 20, 2019
Messages
20
Reaction score
0
Points
1
Excel Version(s)
Excel 2019
I have seen solutions to this sort of problem solved using VBA, but know so little about programming I could not figure out how to apply it to my data.
I have the data below but have also attached the spreadsheet.View attachment guru.xlsx

Here is the input:
Student
Course
Grade
Student 1
ZZZ 129B+
Student 1ZZZ 130B
Student 1ZZZ 131B+
Student 1ZZZ 132A-
Student 2ZZZ 127B+
Student 2ZZZ 128B+
Student 2ZZZ 129C
Student 2ZZZ 130B
Student 2ZZZ 131C+
Student 2ZZZ 132B
Student 3ZZZ 129C
Student 3ZZZ 130B
Student 3ZZZ 131C-
Student 3ZZZ 132B
Student 4ZZZ 125B+
Student 4ZZZ 126B+
Student 4ZZZ 127C
Student 4ZZZ 128B
Student 4ZZZ 129C+
Student 4ZZZ 130B
Student 4ZZZ 131C
Student 4ZZZ 132B

Here is the output I would like. I have hundreds of rows of data and would rather not transpose by hand for each student.
ZZZ 125ZZZ 126ZZZ 127ZZZ 128ZZZ 129ZZZ 130ZZZ 131ZZZ 132
Student 1B+BB+A-
Student 2B+B+CBC+B
Student 3CBC-B
Student 4B+B+CBC+BCB

Any help would be appreciated.
Thank you in advance.
 
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
StudentZZZ 125ZZZ 126ZZZ 127ZZZ 128ZZZ 129ZZZ 130ZZZ 131ZZZ 132
2
Student 1B+BB+A-
3
Student 2B+B+CBC+B
4
Student 3CBC-B
5
Student 4B+B+CBC+BCB
Sheet: Table1

Power Query Mcode
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Course]), "Course", "Grade"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Student", "ZZZ 125", "ZZZ 126", "ZZZ 127", "ZZZ 128", "ZZZ 129", "ZZZ 130", "ZZZ 131", "ZZZ 132"})
in
    #"Reordered Columns"
 
Thank you. This is definitely what I am looking for. But trying to get this into my excel file has cost me more than an hour now. I have never used Power Queries before and looking up tutorials has got me close, but not quite there. I did finally get it to run, but the output was not quite right. I am using Excel 2019.
I open the file. In the data tab, under get and transform data I select from Table/Range and select my data. I open the advanced editor and this is the code that is there
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Course no.", Int64.Type}, {"Course", type text}, {"ID", Int64.Type}, {"Grade", type text}, {"Student Count", Int64.Type}})
in
#"Changed Type"

I replace that with the code given above and used my real data. What comes out is full pivot table with empty cells where where is no letter grade.
Any help would be appreciated.
 
You've got more columns in your source data than you indicated in your first post.
The following is a guess, but attach a realistic workbook to get a solution more likely to work.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Student", "Course", "Grade"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Course]), "Course", "Grade")
in
    #"Pivoted Column"
 
Last edited:
Amazing. That solved the problem. Thanks to the both of you for your help.
:clap2:
 
Back
Top