How to make TEXTJOIN work on a spilled range

user_702977 0
Viewed 178

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

excel-formula excel
1 Answers

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

user_126342300
Related