Convert 2nd column values to rows when column 1 is the same name
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Convert 2nd column values to rows when column 1 is the same name

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert 2nd column values to rows when column 1 is the same name

    I have 2 columns

    Column 1 is the name of a street and that street name is repeated for each task that was completed on the street. I want to group the street name so that there is only 1 line but a new column for each task

    10th street Repair pothole
    10th street New Stop Sign
    10th street New curb
    11th street Repair Pothole
    11th street Add Stoplight

    so the data would look like this when complete

    10th street Repair pothole New stop sign new curb
    11th street Repair pothole Add stoplight

    Is this done with a modulo column? I cannot seem to remember how I figured it out in the past.

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    in which environment are you looking to solve this?

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    Quote Originally Posted by VBA Geek View Post
    in which environment are you looking to solve this?
    Powerquery sorry

  4. #4
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    If you begin with a table, named "Data", like the one below:




    The following M will achieve the below result:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    
    
        Grp = Table.Group(Source, 
                          {"Street"}, 
                            {
                                {"Concat", each Text.Combine([Task],",")},
                                {"Count", each List.Count([Task]), Int64.Type}
                            },
                            GroupKind.Global),
    
    
        Final = Table.RemoveColumns(
                        Table.SplitColumn(Grp, 
                                          "Concat", 
                                           Splitter.SplitTextByDelimiter(","), 
                                           List.Max(Grp[Count]), 
                                            "",
                                            ExtraValues.Ignore),
                        {"Count"}
                        )
    
    
    in
        Final


  5. #5
    Board Regular
    Join Date
    Mar 2016
    Posts
    207
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    This worked brilliantly. I had to add a step in to remove duplicate tasks per asset. THANK YOU!!!!!

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    I dont suppose you have any idea on how this code could be run but with python instead? I am generating a spreadsheet using python and I also wish to manipulate the fields like this within the code

  7. #7
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    In Python you can do it like this using pandas

    Code:
    import pandas as pd
    
    
    data = {'Street': ['10th street'] * 3 + ['11th street'] * 2,
            'task': ['Repair Pothole', 'New Stop Sign', 'New Curb', 'Repair Pothole', 'Add StopLight']}
    
    
    df = (pd.DataFrame(data)
          .groupby(by='Street')
          .apply(lambda grouped: grouped['task'].str.cat(sep="|"))
          .str.split(pat="|", expand=True))
    
    
    print(df)
    Last edited by VBA Geek; Mar 18th, 2019 at 10:15 AM.
    Excel and BI blog: http://xcelanz.com/

  8. #8
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    Many thanks, is there a way to write this without typing out the data ?

  9. #9
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,857
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    yes, but this depends on your data source. pandas supports various data sources you can build a dataframe from
    Excel and BI blog: http://xcelanz.com/

  10. #10
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert 2nd column values to rows when column 1 is the same name

    Another option:

    1- ) Select your range and then insert a Pivot Table
    2- ) Within the options select "Add this to the Data Model"
    3- ) In Pivot Table drag Column1 fiel to "Rows"
    4- ) Then in Pivot Table panel to the left, Rigth Click over your Table name and the option "Add Measure" will appear
    5- ) Now in "Measure name" you could put something like "Task" as field name.
    6- ) Wthing formula section enter this =CONCATENATEX(Table1,Table1[Column2],", ")
    7- ) In Pivot Table panel will appears a new field "Fx Tasks"
    8- ) Select Fx Task and drag it to "Values" window.
    9- ) Remove Grand Totals row
    10-) Done
    Last edited by Fractalis; Mar 18th, 2019 at 07:13 PM.

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
  •