Sep 16 2023 05:34 AM
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 in 1 column . I'd like to create a new table with every child listed in one column, and a single cell with names of their support workers deliminated by comma. I know i could concatenate each short list but i would like a dynamic solution as some children have 6 support workers, others only 1 or 2. I had some initial success with
2 formulas i gleened from ChatGPT but now that solutions does not seem to work ( and it was on the data set and not the pivot table)
First made a column with this formula :
It gave me a huge string text-joining every support worker entry for every child... with heaps of duplicates.
=TEXTJOIN(", ", TRUE, IF($E$2:$E$3963=E77, $D$2:$D$3963, ""))
Then i used which removed the duplicates but now is glitchy... besides i don't understand how it ever worked.
=TEXTJOIN(", ", TRUE, UNIQUE(FILTERXML("<root><element>" & SUBSTITUTE(N63, ", ", "</element><element>") & "</element></root>", "//element")))
Would love to use power query instead of formulas but happy for any help at all :)
Sep 16 2023 07:07 AM - edited Sep 16 2023 07:13 AM
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)
)
Sep 16 2023 07:20 AM