Results 1 to 5 of 5

Thread: Transpose and group? How do i transpose one column into multiple rows base on value in another column

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,189
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Transpose and group? How do i transpose one column into multiple rows base on value in another column

    Hello, this seems like it should be easy but I can't find ananswer. I have tried the native pivot function,transpose, etc. I have two columns likethis: (sorry for the bad formatting)

    Column 1 / Column 2
    A / Apple
    B / Banana
    C / Carrot
    A / Airplane
    B / Boat
    C / Car
    A / Anything

    So I need Column 1 grouped (unique values only), and Column2 to be spread across as many columns as necessary like:
    A / Apple / Airplane / Anything
    B / Banana / Boat / Null
    C / Carrot / Car / Null

    I hope that is clear

    Thank you for any help.

    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,799
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose and group? How do i transpose one column into multiple rows base on value in another column

    Using Power Query /Get and Transform here is the Mcode and the end result

    v A B C D E F G H
    1 Column1 1 2 3 4 5 6 7
    2 A Apple Airplane Anything
    3 B Banana Boat
    4 C Carrot Car
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
    in
        #"Pivoted Column"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,313
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Transpose and group? How do i transpose one column into multiple rows base on value in another column

    try

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
        List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
        Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv))
    in
        Split
    Column1 Column2 Column1 Custom.1 Custom.2 Custom.3
    A Apple A Apple Airplane Anything
    B Banana B Banana Boat
    C Carrot C Carrot Car
    A Airplane
    B Boat
    C Car
    A Anything
    Last edited by sandy666; Jun 18th, 2019 at 10:24 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    Board Regular
    Join Date
    Nov 2005
    Location
    Clearwater, Florida
    Posts
    1,189
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose and group? How do i transpose one column into multiple rows base on value in another column

    Thank you for all of the help, Sandy works perfect thanks
    forum use guidelines; forum rules; terms of use; FAQs Use code tags [ Code ] your code here [ /Code ] Try searching for your answer first, see how

    Work - 32 bit Office 2016 Win10 .... Home - Office 365 Win10

    I solve for X but don't know Y


  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,313
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Transpose and group? How do i transpose one column into multiple rows base on value in another column

    You are welcome
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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