Solving this problem with dynamic array formula

[tm23]

Dear all,
How would you solve the concatenation of the column comment, without vba and using only one dynamic array formula in E2
So far, I have something like this, but this is not dynamic, even if you add D2#
=JOINTEXT("/",TRUE,IF($A$2:$A$5=D2,$B$2:$B$5,""))
Thanks for any idea.

ID	Comment		ID	Comment concatenated
15​	AA		12​	CC/DD
16​	BB		16​	BB
12​	CC
12​	DD

 

[tm23]

I am falling in love with LET !

I cleaned up the formula, used the same syntax as xlookup, and added a couple more argument to manage the "if_not_found".

If you see ways to simplify it further or if you identify cases where it will not work, please let me know

simplified problem3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	Comment		ID		These solutions work, but no spills				Solution with spills		Dummy data for col. A and B			Dummy data for the col. D
2	15	AA		12		CC|DD	CC/DD	CC/DD		CC|DD		15	AA		12
3	16	BB		15		AA	AA	AA		AA		16	BB		15
4	12	CC		99		#CALC!		#CALC!		No result		12	CC		99
5	12	DD										12	DD
Feuil1

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER(L2:L5,L2:L5>0)
D2:D4	D2	=FILTER(O2:O4,O2:O4>0)
F2:F4	F2	=TEXTJOIN("|",TRUE,FILTER($B$2#,$A$2#=D2))
G2:G4	G2	=TEXTJOIN("/",TRUE,IF($A$2#=D2,$B$2#,""))
J2:J4	J2	=LET( lookup_values,D2#, lookup_array,A2#, return_array_to_concat,B2#, col_range_to_concat,B:B, separator_row,"\", separator_col,"|", if_not_found,"No result", array_before_split,IFERROR(INDEX(col_range_to_concat,IF((lookup_values=TRANSPOSE(lookup_array)),TRANSPOSE(ROW(return_array_to_concat)),"")),separator_col), CountCol_array_before_split,COLUMNS(array_before_split), array_to_split,TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_array_before_split,1,1,1/(IF(CountCol_array_before_split=1,1,CountCol_array_before_split-1))),0)>1,separator_row,separator_col),0,array_before_split), pre_final_result,SUBSTITUTE(TRANSPOSE(INDEX((FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN(separator_row,,array_to_split),separator_row,"</a><a>")&"</a></b>","//a")),SEQUENCE(ROWS(array_to_split),LEN(INDEX(array_to_split,1,1))-LEN(SUBSTITUTE(INDEX(array_to_split,1,1),separator_row,))+1))),separator_col&separator_col,""), IF(pre_final_result=separator_col,if_not_found,pre_final_result))
H2:H5	H2	=IF(D2="","",TEXTJOIN("/",1,FILTER(INDEX($B$2#,0,1),INDEX($A$2#,0,1)=D2)))
Dynamic array formulas.

 

[jaeiow]

@tm23

I am falling in love with LET !

I cleaned up the formula, used the same syntax as xlookup, and added a couple more argument to manage the "if_not_found".

If you see ways to simplify it further or if you identify cases where it will not work, please let me know

simplified problem3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID	Comment		ID		These solutions work, but no spills				Solution with spills		Dummy data for col. A and B			Dummy data for the col. D
2	15	AA		12		CC|DD	CC/DD	CC/DD		CC|DD		15	AA		12
3	16	BB		15		AA	AA	AA		AA		16	BB		15
4	12	CC		99		#CALC!		#CALC!		No result		12	CC		99
5	12	DD										12	DD
Feuil1

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER(L2:L5,L2:L5>0)
D2:D4	D2	=FILTER(O2:O4,O2:O4>0)
F2:F4	F2	=TEXTJOIN("|",TRUE,FILTER($B$2#,$A$2#=D2))
G2:G4	G2	=TEXTJOIN("/",TRUE,IF($A$2#=D2,$B$2#,""))
J2:J4	J2	=LET( lookup_values,D2#, lookup_array,A2#, return_array_to_concat,B2#, col_range_to_concat,B:B, separator_row,"\", separator_col,"|", if_not_found,"No result", array_before_split,IFERROR(INDEX(col_range_to_concat,IF((lookup_values=TRANSPOSE(lookup_array)),TRANSPOSE(ROW(return_array_to_concat)),"")),separator_col), CountCol_array_before_split,COLUMNS(array_before_split), array_to_split,TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_array_before_split,1,1,1/(IF(CountCol_array_before_split=1,1,CountCol_array_before_split-1))),0)>1,separator_row,separator_col),0,array_before_split), pre_final_result,SUBSTITUTE(TRANSPOSE(INDEX((FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN(separator_row,,array_to_split),separator_row,"</a><a>")&"</a></b>","//a")),SEQUENCE(ROWS(array_to_split),LEN(INDEX(array_to_split,1,1))-LEN(SUBSTITUTE(INDEX(array_to_split,1,1),separator_row,))+1))),separator_col&separator_col,""), IF(pre_final_result=separator_col,if_not_found,pre_final_result))
H2:H5	H2	=IF(D2="","",TEXTJOIN("/",1,FILTER(INDEX($B$2#,0,1),INDEX($A$2#,0,1)=D2)))
Dynamic array formulas.

I would like to discuss this current formula and inquire as to whether there is a better way to deal with a longer list of values. The max lookup row limit seems to be around 76

 

[Fluff]

Hi & welcome to MrExcel
You would be better of starting a thread of your own & explaining exactly what you are trying to do, along with posting some sample data. There are some newer functions that would make things easier.

 

[Quiet_Nectarine_]

@tm23


I would like to discuss this current formula and inquire as to whether there is a better way to deal with a longer list of values. The max lookup row limit seems to be around 76

It is 2 years later but if you still need a simple solution, you can use the MAP function instead.

=MAP(D2#,LAMBDA(num,TEXTJOIN("/",TRUE,IF($A$2:$A$5=num,$B$2:$B$5,""))))