Search code examples
excelexcel-formulaexcel-365

Merge cells of text using filter and dynamic array

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 :

enter image description here

And I want this table :

enter image description here

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

Solution

  • You need to use the following formula :

    enter image description here

    • 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:

    enter image description here

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