Results 1 to 5 of 5

Thread: Transpose uneven data sets

  1. #1
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016

    Transpose uneven data sets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.guru.xlsx

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

    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 125 ZZZ 126 ZZZ 127 ZZZ 128 ZZZ 129 ZZZ 130 ZZZ 131 ZZZ 132
    Student 1 B+ B B+ A-
    Student 2 B+ B+ C B C+ B
    Student 3 C B C- B
    Student 4 B+ B+ C B C+ B C B

    Any help would be appreciated.
    Thank you in advance.

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    309
    Articles
    0
    Excel Version
    2019
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Student ZZZ 125 ZZZ 126 ZZZ 127 ZZZ 128 ZZZ 129 ZZZ 130 ZZZ 131 ZZZ 132
    2
    Student 1 B+ B B+ A-
    3
    Student 2 B+ B+ C B C+ B
    4
    Student 3 C B C- B
    5
    Student 4 B+ B+ C B C+ B C B
    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"

  3. #3
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    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.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,938
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2021-03-07 at 02:12 PM.

  5. #5
    Seeker spudulene's Avatar
    Join Date
    Aug 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 2016
    Amazing. That solved the problem. Thanks to the both of you for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •