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

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
217
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If you begin with a table, named "Data", like the one below:

FA2kg2J.png



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


nWZIM8n.png
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
yes, but this depends on your data source. pandas supports various data sources you can build a dataframe from
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top