2

I have the following data in C4:D10 as input. Cell G4 has the formula =UNIQUE(C4:C10), Cell H4 has the formula =TEXTJOIN(", ",TRUE,IF($C$4:$C$10=G4,$D$4:$D$10,"")), and Cell H5 has =TEXTJOIN(", ",TRUE,IF($C$4:$C$10=G5,$D$4:$D$10,"")).

The values in H4 and H5 are what I need: they combine the texts for each category. However, I would prefer to write one array formula by TEXTJOIN and over G4#. I tried =TEXTJOIN(", ",TRUE,IF($C$4:$C$10=G4#,$D$4:$D$10,"")), it did not work well.

Does anyone know how to write such one formula over G4# to achieve the same values?

PS: formulas with LAMBDA and helper functions will still be appreciated if there is no other choice.

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
SoftTimur
  • 5,630
  • 38
  • 140
  • 292
  • 1
    Do you have `BYROW()`? – JvdV Dec 08 '21 at 08:35
  • Isn't `BYROW` a helper function? If you think there is no other choice, a solution with LABMDA and helper functions are welcome too. – SoftTimur Dec 08 '21 at 08:36
  • 2
    Right (not sure what you meant with 'helper' functions), I don't have access to it myself on this specific laptop, but it should be something like `=BYROW(G4#,LAMBDA(a,TEXTJOIN(", ",,FILTER(D4:D10,C4:C10=a))))` – JvdV Dec 08 '21 at 08:41

1 Answers1

3

You could achieve this with the formula below:

=LET(data,FILTER(C:D,C:C<>""),
          dataC1,INDEX(data,,1),
          dataC2,INDEX(data,,2),
          UdataC1,G4#,
                  v,UNIQUE(TRANSPOSE(IF(dataC1=TRANSPOSE(UdataC1),dataC2,"")),1),
                         seqrv,SEQUENCE(ROWS(v)),
           sep,",  ",
                         x,MMULT((LEN(v)+LEN(sep))*(v<>""),SIGN(SEQUENCE(COLUMNS(v))))-LEN(sep),
                         y,MMULT(--(TRANSPOSE(seqrv)<seqrv),x+LEN(sep)-1)+seqrv,
IFERROR(MID(TEXTJOIN(sep,1,v),y,x),""))

enter image description here

Or you can spill it all in one go using:

=LET(data,FILTER(C:D,C:C<>""),
          dataC1,INDEX(data,,1),
          dataC2,INDEX(data,,2),
          UdataC1,UNIQUE(dataC1),
                         v,UNIQUE(TRANSPOSE(IF(dataC1=TRANSPOSE(UdataC1),dataC2,"")),1),
                         seqrv,SEQUENCE(ROWS(v)),
           sep,",  ",
                         x,MMULT((LEN(v)+LEN(sep))*(v<>""),SIGN(SEQUENCE(COLUMNS(v))))-LEN(sep),
                         y,MMULT(--(TRANSPOSE(seqrv)<seqrv),x+LEN(sep)-1)+seqrv,
          join,IFERROR(MID(TEXTJOIN(sep,1,v),y,x),""),
CHOOSE({1,2},UdataC1,join))

enter image description here

with lots of help from this answer from Mark Fitzpatrick

P.b
  • 8,293
  • 2
  • 10
  • 25