SOLVED

Grouping by a column and merging multiple cell values into a single cell separated by commas

Microsoft

I have a table with the list of accounts and their corresponding account managers. Some accounts have multiple account managers thus the account number is repeated multiple times in the report. I'm trying to write a formula/macro/query to merge all these account managers into a single cell and separate them with commas. Is this possible? Below is the sample data and my desired end table.

Sample Data:

Account Name Account Manager
ABC Inc John Smith
ABC Inc Adam Brown
BCD Inc Josh Richards
BCD Inc John Smith
BCD Inc Tristan Chapman
XYZ Inc Kyle Hunter

 

Desired Table:

Account Name Account Manager
ABC Inc John Smith, Adam Brown
BCD Inc Josh Richards, John Smith, Tristan Chapman
XYZ Inc Kyle Hunter

 

 

2 Replies
best response confirmed by bur10012 (Microsoft)
Solution

@bur10012 

Maybe you want to do this with Power Query.

@bur10012 

If you are loading the data from an external source (ETL) then PQ is the hand-down winner.  If the data is already loaded within a 365 workbook, then formulas will perform the task perfectly well.

= MAP(UNIQUE(acName),
      LAMBDA(ac, TEXTJOIN(", ",,FILTER(acMgr, acName=ac)))
  )