Merge cells of text using filter and dynamic array

Question

In the same way of my question about the double value Remove double values and sum the values, I need to concatenate textual value in function a filter value, let see the table :

And I want this table :

I'm using dynamic array, and I want that the output formula are also dynamic array.

Now, the only formula that I can get is this one:

[fr]

 =JOINDRE.TEXTE("/";VRAI;CHOISIRCOLS(FILTRE(B1#;PRENDRE(B1#;;1)=H1);2))

[en]

=TEXTJOIN("/", TRUE, CHOOSECOLS( FILTER( B1#, TAKE( B1#,,1) = H1),2))

But, I want to replace H1 by H1# but it doesn't work.

What is the best way to get it ? Thank's

Answer 1

You need to use the following formula :

---

• Formula used in cell E2

=LET(
     a, B2:B11,
     b, C2:C11,
     ua, UNIQUE(a),
     HSTACK(SEQUENCE(ROWS(ua)),ua, BYROW(ua, LAMBDA(x, TEXTJOIN("/",,FILTER(b,a=x))))))

---

Or, with GROUPBY() applicable to MS365 Beta Channel Version for now:

---

=LET(
     a, GROUPBY(B1:B11,C1:C11,ARRAYTOTEXT,,0),
     HSTACK(SEQUENCE(ROWS(a)),a))

---