filtering dynamically a table In Excel-CodePudding

Is there a way to have a dynamic array formula that gives the solution as per cells A11:B12 out of the Table A1:B6.

• Formula used in cell A10

=HSTACK(UNIQUE(Table23[Client]),BYROW(UNIQUE(Table23[Client]),LAMBDA(x,TEXTJOIN(",",,REPT(Table23[Product],x=Table23[Client])))))

You can wrap the whole within a LET() to make it more readable, like as below,

• Formula used in cell A10

=LET(_client,UNIQUE(Table23[Client]),
HSTACK(_client,BYROW(_client,LAMBDA(x,TEXTJOIN(",",,REPT(Table23[Product],x=Table23[Client]))))))

You can use a FILTER() function in place of REPT()

• Formula used in cell A10

=LET(_client,UNIQUE(Table23[Client]),
HSTACK(_client,BYROW(_client,LAMBDA(x,TEXTJOIN(",",,FILTER(Table23[Product],x=Table23[Client]))))))

You can also try in this way as well,

• Formula used in cell A9

=DROP(IFERROR(REDUCE("",UNIQUE(Table23[Client]),LAMBDA(a,b,VSTACK(a,HSTACK(b,TEXTJOIN(",",1,FILTER(Table23[Product],Table23[Client]=b)))))),""),1)