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!

CodePudding user response：

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.

CodePudding user response：

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.

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