Mar 17 2022 07:49 AM
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 |
Mar 17 2022 08:00 AM
SolutionMaybe you want to do this with Power Query.
Mar 17 2022 09:11 AM
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)))
)
Mar 17 2022 08:00 AM
Solution