Rolling multiple rows into a one row in excel

user_11760941 0
Viewed 183

I'm using google sheets/excel to combine all the unique locations associated with unique role with a single email.

Example

Outcome

I just can't think of a way to start this. I thought maybe I can do something like on Column D

=if(a2=a1,concatenate(c1, ", ", 2)) the clean that out on Column E etc but don't think it'll work well with a file that has over 10,000 rows.

Any advice is appreciated!

excel-formula excel
2 Answers

If you have Office 365, I suggest:

  • Create a table from your original data (Home=>Format as table)
F1: Email       G1: Role     H1: Location

F2:  =UNIQUE(INDEX(emails,SEQUENCE(ROWS(emails)),{1,2}))

G2:  =TEXTJOIN(", ",TRUE,FILTER(emails[Location],((F2=emails[Email])*(G2=emails[Role]))))

Select G2 and fill down as far as needed.

enter image description here

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)

To use Power Query

  • Select some cell in your Data Table
  • Data => Get&Transform => from Table/Range or from within sheet
  • When the PQ Editor opens: Home => Advanced Editor
  • Make note of the Table Name in Line 2
  • Paste the M Code below in place of what you see
  • Change the Table name in line 2 back to what was generated originally.
  • The algorithm Groups by Email and Role; and then returns the Locations as a concatenated list

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="emails"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Email", type text}, {"Role", type text}, {"Location", type text}}),
    group = Table.Group(#"Changed Type",{"Email","Role"},{
        {"Locations", each Text.Combine([Location],", "), type text}
    })
in
    group
user_28729220

If the data is sorted like you have it, you can do this. In Column D:

=IF(B2 <> B3, B2, "")

Then set E2 = C2, and in E3 do:

=IF(B3 <> B2, C3, CONCATENATE(E2, ",", C3))

After dragging these down you can filter on Column D not blank and get your end result.

This same thing is more easily accomplished with Data > Outline > Subtotal on the range when doing straight math instead of concatenation, but that's not your requirement here.

user_56133670
Related