Forum Discussion
finding unique support workers in a data set and then grouping them along side the corresponding
Hi, i have a pivot table with child name in the rows and also support worker in rows. What i get is a short list of all the support workers each child has had support sessions with.... but it's all i...
It all depends upon the version of Excel. Solutions I write for 365 are not remotely similar to anything that might have been done for traditional spreadsheets.
= LET(
distinctChild, UNIQUE(child),
associatedWorkers, MAP(distinctChild,
LAMBDA(name,
LET(
worker, FILTER(supportWorker, child=name),
distinct, UNIQUE(worker),
TEXTJOIN(", ",,distinct)
)
)
),
HSTACK(distinctChild, associatedWorkers)
)
With named Lambda functions, this can be tidied up but, maybe, that is a step too far!
= LET(
distinctChild, UNIQUE(child),
associatedWorkers, MAP(distinctChild, ListSupportλ),
HSTACK(distinctChild, associatedWorkers)
)
- @https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/214174
Thankyou so much for the neat solution! I downloaded your file and it runs on my computer. I will have to try it at work. Fingers crossed! I will let you know 🙂
