finding unique support workers in a data set and then grouping them along side the corresponding

Question

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 🙂

jonbastians · Answer

@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 🙂

peterbartholomew1 · Answer

JonBastians&nbsp;It all depends upon the version of Excel.&nbsp; Solutions I write for 365 are not remotely similar to anything that might have been done for traditional spreadsheets.&nbsp;&nbsp;= LET(
    distinctChild,     UNIQUE(child),
    associatedWorkers, MAP(distinctChild,
      LAMBDA(name,
        LET(
          worker,      FILTER(supportWorker, child=name),
          distinct,    UNIQUE(worker),
          TEXTJOIN(", ",,distinct)
        )
      )
    ),
    HSTACK(distinctChild, associatedWorkers)
  )&nbsp;With named Lambda functions, this can be tidied up but, maybe, that is a step too far!&nbsp;= LET(
    distinctChild,     UNIQUE(child),
    associatedWorkers, MAP(distinctChild, ListSupportλ),
    HSTACK(distinctChild, associatedWorkers)
  )&nbsp;&nbsp;

Forum Discussion

finding unique support workers in a data set and then grouping them along side the corresponding

2 Replies

Resources