Forum Discussion
Solved
Grouping by a column and merging multiple cell values into a single cell separated by commas
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 |
Maybe you want to do this with Power Query.
2 Replies
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))) )Maybe you want to do this with Power Query.
