Forum Discussion

ethinix's avatar
ethinix
Copper Contributor
Dec 05, 2023
Solved

How to merge multiple rows with same data except one column with different data.

In the image above, I have multiple rows of data which are the same across all columns except one, that is Delivery Zip Code, where the data is different on all rows.

I want to merge these 4 rows in to one row and all the data from Delivery Zipcode to be added in to one column seperated by a comma. Rest of the duplicate rows to be deleted.

In the above example, there will be one row with the Delivery Zipcode column containing 36310, 36317, 36373.

Any help will be greatly appreciated.

excel
Formulas and Functions
Macros and VBA
office 365
  • Lorenzo's avatar
    Lorenzo
    Dec 05, 2023

    Hi ethinix 

     

    With Power Query. Grouping of records based on Latitude & Longitude:

     

    // ZipCodesConcat
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceNames = Table.ColumnNames(Source),
    Keys = {"Latitude", "Longitude"},
    TypedZipCode = Table.TransformColumnTypes(Source,
        {{"DELIVERY ZIPCODE", type text}}
    ),
    GroupedByLatLong = Table.Group(TypedZipCode, Keys,
        {
            {"DELIVERY ZIPCODE", each Text.Combine([DELIVERY ZIPCODE], ", ")},
            {"DATA", each Table.FirstN(_, 1), type table}
        }
    ),
    Expanded = Table.ExpandTableColumn(GroupedByLatLong, "DATA",
        List.Difference(SourceNames, Keys & {"DELIVERY ZIPCODE"})
    ),
    Reordered = Table.SelectColumns(Expanded, SourceNames)
in
    Reordered

     

     

    In attached file:
    - Put your data in Table1 (sheet 'Input'), adding your extra column(s) that don't appear on your picture
    - Switch to sheet 'Output' > Right-click in table ZipCodesConcat > Refresh

     

    (EDIT) Assumptions made:
    #1 [Latitude] + [Longitude] are always provided (no blank/null)
    #2 The input doesn't contain something like (otherwise the grouping must be revised):

    PHYSICAL CITYLatitudeLongitude
    BERLIN52.524413.4105
    AMSTERDAM52.524413.4105
    AMSTERDAM52.524413.4105
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 05, 2023

    ethinix 

     

    I briefly saw a post from you then it suddenly disappeard (website bug?). If important re-post please & check the EDIT on my initial post

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Dec 05, 2023

    ethinix 

    if with sql,

     

    select f01,f02,f03,f04,f05, group_concat(f06),f07, f08,f09,f10,f11 from consolidateSheet group by f01,f02,f03,f04,f05,f07,f08,f09,f10,f11;

     

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 05, 2023

    Hi ethinix 

     

    With Power Query. Grouping of records based on Latitude & Longitude:

     

    // ZipCodesConcat
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SourceNames = Table.ColumnNames(Source),
    Keys = {"Latitude", "Longitude"},
    TypedZipCode = Table.TransformColumnTypes(Source,
        {{"DELIVERY ZIPCODE", type text}}
    ),
    GroupedByLatLong = Table.Group(TypedZipCode, Keys,
        {
            {"DELIVERY ZIPCODE", each Text.Combine([DELIVERY ZIPCODE], ", ")},
            {"DATA", each Table.FirstN(_, 1), type table}
        }
    ),
    Expanded = Table.ExpandTableColumn(GroupedByLatLong, "DATA",
        List.Difference(SourceNames, Keys & {"DELIVERY ZIPCODE"})
    ),
    Reordered = Table.SelectColumns(Expanded, SourceNames)
in
    Reordered

     

     

    In attached file:
    - Put your data in Table1 (sheet 'Input'), adding your extra column(s) that don't appear on your picture
    - Switch to sheet 'Output' > Right-click in table ZipCodesConcat > Refresh

     

    (EDIT) Assumptions made:
    #1 [Latitude] + [Longitude] are always provided (no blank/null)
    #2 The input doesn't contain something like (otherwise the grouping must be revised):

    PHYSICAL CITYLatitudeLongitude
    BERLIN52.524413.4105
    AMSTERDAM52.524413.4105
    AMSTERDAM52.524413.4105
    • ethinix's avatar
      ethinix
      Copper Contributor
      Dec 05, 2023

      Lorenzo The lattitude and logitude are missing in 1/3 of the file. Is it possible to include all the ZIP in one row based on the Physical City alone? I mean the first column.

       

Resources