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

 

[mamady]

Hi Dear,

Please try the below

Book1
	A	B	C	D	E
1	ID	Comment		ID	Comment concatenated
2	15	AA		12	CC/DD
3	16	BB		15	AA
4	12	CC		16	BB
5	12	DD
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=SORT(UNIQUE(A2:A5),,1)
E2:E4	E2	=TEXTJOIN("/",TRUE,IF($A$2:$A$5=D2,$B$2:$B$5,""))
Dynamic array formulas.

Regards

 

[mamady]

You can change both ranges to Excel Tables in order to cater for any additional data points added in column A&B

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E
1	ID	Comment		ID	Comment concatenated
2	15	AA		12	CC/DD
3	16	BB		15	AA
4	12	CC		16	BB
5	12	DD
6
Master

Cell Formulas
Range	Formula
D2:D4	D2	=SORT(UNIQUE(FILTER(A2:A20,A2:A20<>"")))
E2:E4	E2	=TEXTJOIN("/",,FILTER($B$2:$B$20,$A$2:$A$20=D2))
Dynamic array formulas.

 

[tm23]

Thank you all for your suggestions.

Let me clarify my problem.

A2:B5 is the result of a dynamic array formula. So it will auto-expand.
D2:D4 is also the result of a dynamic array formula. So it will auto-expand.

In E2, I would like to have a dynamic array formula that will also auto-expand according to the number of cells in D2:D4 or D2#

Fluff's formula is working, but I need to copy-down it.

This one (with a # after the D2) is not working and return #REF
=TEXTJOIN("/",,FILTER($B$2:$B$20,$A$2:$A$20=D2#))

I hope I am clearer.

Thank you for any help.

 

[tm23]

Sorry, it returns #N/A, but still the same problem, it does not auto-expand.

 

[tm23]

Classeur2
	A	B	C	D	E	F
1	ID	Comment		ID	Comment concatenated
2	15	AA		12	#N/A	#N/A
3	16	BB		15
4	12	CC		16
5	12	DD
6
Feuil1

Cell Formulas
Range	Formula
D2:D4	D2	=SORT(UNIQUE(A2:A5),,1)
E2	E2	=TEXTJOIN("/",TRUE,FILTER($B$2:$B$20,$A$2:$A$20=D2#))
F2	F2	=TEXTJOIN("/",TRUE,IF($A$2:$A$5=D2#,$B$2:$B$5,""))
Dynamic array formulas.

 

[tm23]

Ok, so after several hours, I think I have something.
There should be no | and \ in the comments (but this is relatively easy to check).
References for filterxml : SUMCOLS and SUMROWS functions for Dynamic Arrays

simplified problem.xlsm
	A	B	C	D	E	F	G
1	ID	Comment		ID	Comment concatenated		Final solution :
2	12	AA		12	#N/A	#N/A	AA
3	13	BB		13			BB|DD
4	15	CC		15			CC
5	13	DD
Feuil1

Cell Formulas
Range	Formula
D2:D4	D2	=SORT(UNIQUE(A2:A5),,1)
E2	E2	=TEXTJOIN("/",TRUE,FILTER($B$2:$B$20,$A$2:$A$20=D2#))
F2	F2	=TEXTJOIN("/",TRUE,IF($A$2:$A$5=D2#,$B$2:$B$5,""))
G2:G4	G2	=LET( range1,$A$2:$A$5, range_to_concatenate,$B$2:$B$5, col_range_to_concat,B:B, range2,D2#, delimiteur_ligne,"\", range_before_split,IFERROR(INDEX(col_range_to_concat,IF((range2=TRANSPOSE(range1)),TRANSPOSE(ROW(range_to_concatenate)),"")),"|"), CountCol_range_before_split,COLUMNS(range_before_split), range_to_split,TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_range_before_split,1,1,1/(CountCol_range_before_split-1)),0)>1,delimiteur_ligne,"|"),0,range_before_split), SUBSTITUTE( TRANSPOSE(INDEX(TRANSPOSE(FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN(delimiteur_ligne,,range_to_split),delimiteur_ligne,"</a><a>")&"</a></b>","//a")), SEQUENCE(ROWS(range_to_split), LEN(INDEX(range_to_split,1,1))-LEN(SUBSTITUTE(INDEX(range_to_split,1,1),delimiteur_ligne,))+1))), "||",""))
Dynamic array formulas.

 

[Peter_SSs]

In E2, I would like to have a dynamic array formula that will also auto-expand according to the number of cells in D2:D4 or D2#

The problem with that is that it is a TEXTJOIN. If you try to make it apply to the whole spill range then I think you will find that it either will not work at all or your will get unwanted values in the textjoin for each row. I would be happy if shown to be wrong about that though.

This (E2) could be copied down as far as you might ever need. The range that the FILTER applies to in the column E formula will auto-expand though, unlike the earlier suggestions.
Ignore my A2 formula - I had to manufacture something to get a spill result in columns A:B.

I have also given a suggestion for an auto-expanding formula for column D rather than the fixed range that you have used.

tm23 1.xlsm
	A	B	C	D	E
1	ID	Comment		ID	Comments
2	15	AA		12	CC/DD
3	16	BB		15	AA
4	12	CC		16	BB
5	12	DD
6
Sheet1

Cell Formulas
Range	Formula
A2:B5	A2	=FILTER(N2:O20,(N2:N20<20)*(N2:N20<>0))
D2:D4	D2	=SORT(UNIQUE(INDEX(A2#,0,1)))
E2:E6	E2	=IF(D2="","",TEXTJOIN("/",1,FILTER(INDEX(A$2#,0,2),INDEX(A$2#,0,1)=D2)))
Dynamic array formulas.

 

[tm23]

Dear Peter,
Thank you for your answer and comments.
I will try to explain the general idea on how to make the TEXTJOIN spills, although this is not a "real" native spill, but the result looks the same. I have some concerns regarding speed and maximum amount of data. I will test it soon in my "real-life" situation.

[Edit to my previous post] : I gathered the information on the FILTERXML here : Add a Split dynamic array function

The general idea is to create an array with the different comments, then to textjoin these comments with a separator for rows and another one for columns, then to split again, but this time, only by rows using firlterxml.
I also modified the formula to consider the more general case where some ID in column D would not be found in column A (obviously not a unique formula then).

Details :

simplified problem3.xlsm
	L	M	N	O	P	Q
1	Explanations
2
3	Definitions of the first set of variables with LET
4	=LET( range1;A2#; range_to_concatenate;B2#; col_range_to_concat;B:B; range2;D2#; delimiteur_ligne;"\";"")
5
6	Definition of range_before_split:
7	The general idea is to create an array between A2# and D2#, then to match the row number, and then the value of B2#
8	=LET(range1;A2#;range_to_concatenate;B2#;col_range_to_concat;B:B;range2;D2#;separator_row;"\"; SIERREUR(INDEX(col_range_to_concat;SI((range2=TRANSPOSE(range1));TRANSPOSE(LIGNE(range_to_concatenate));""));"|"))
9	|	|	CC	DD
10	AA	|	|	|
11	|	BB	|	|
12
13	Details of this calculation:
14	We look for the row of the intersection of A2# and D2#
15	=SI((D2#=TRANSPOSE(A2#));TRANSPOSE(LIGNE(B2#));"")
16			4	5
17	2
18		3
19
20	We use the INDEX to return the value of B2# (the comment)
21	If we can't find it, we replace with "|"
22	=SIERREUR(INDEX(B:B;SI((D2#=TRANSPOSE(A2#));TRANSPOSE(LIGNE(B2#));""));"|")
23	|	|	CC	DD
24	AA	|	|	|
25	|	BB	|	|
26
27	Group the array in one string with separator for rows and for columns
28
29	=LET(range1;A2#;range_to_concatenate;B2#;col_range_to_concat;B:B;range2;D2#;separator_row;"\"; range_before_split;SIERREUR(INDEX(col_range_to_concat;SI((range2=TRANSPOSE(range1));TRANSPOSE(LIGNE(range_to_concatenate));""));"|"); CountCol_range_before_split;COLONNES(range_before_split); JOINDRE.TEXTE(SI(ARRONDI.INF(SEQUENCE(CountCol_range_before_split;1;1;1/(SI(CountCol_range_before_split=1;1;CountCol_range_before_split-1)));0)>1;separator_row;"|");0;range_before_split))
30	||||CC|DD\AA||||||\||BB||||
31
32	Use FILTERXML to split by rows + remove useless separators
33	=LET(range1;A2#;range_to_concatenate;B2#;col_range_to_concat;B:B;range2;D2#;separator_row;"\"; range_before_split;SIERREUR(INDEX(col_range_to_concat;SI((range2=TRANSPOSE(range1));TRANSPOSE(LIGNE(range_to_concatenate));""));"|"); CountCol_range_before_split;COLONNES(range_before_split); range_to_split;JOINDRE.TEXTE(SI(ARRONDI.INF(SEQUENCE(CountCol_range_before_split;1;1;1/(SI(CountCol_range_before_split=1;1;CountCol_range_before_split-1)));0)>1;separator_row;"|");0;range_before_split); SUBSTITUE( TRANSPOSE(INDEX((FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(separator_row;;range_to_split);separator_row;"</a><a>")&"</a></b>";"//a")); SEQUENCE(LIGNES(range_to_split); NBCAR(INDEX(range_to_split;1;1))-NBCAR(SUBSTITUE(INDEX(range_to_split;1;1);separator_row;))+1))); "||";""))
34	CC|DD
35	AA
36	BB
Feuil1

Cell Formulas
Range	Formula
L4,L33,L29,L22,L15,L8	L4	=FORMULATEXT(L5)
L5	L5	=LET( range1,A2#, range_to_concatenate,B2#, col_range_to_concat,B:B, range2,D2#, delimiteur_ligne,"\","")
L9:O11	L9	=LET(range1,A2#,range_to_concatenate,B2#,col_range_to_concat,B:B,range2,D2#,separator_row,"\", IFERROR(INDEX(col_range_to_concat,IF((range2=TRANSPOSE(range1)),TRANSPOSE(ROW(range_to_concatenate)),"")),"|"))
L16:O18	L16	=IF((D2#=TRANSPOSE(A2#)),TRANSPOSE(ROW(B2#)),"")
L23:O25	L23	=IFERROR(INDEX(B:B,IF((D2#=TRANSPOSE(A2#)),TRANSPOSE(ROW(B2#)),"")),"|")
L30	L30	=LET(range1,A2#,range_to_concatenate,B2#,col_range_to_concat,B:B,range2,D2#,separator_row,"\", range_before_split,IFERROR(INDEX(col_range_to_concat,IF((range2=TRANSPOSE(range1)),TRANSPOSE(ROW(range_to_concatenate)),"")),"|"), CountCol_range_before_split,COLUMNS(range_before_split), TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_range_before_split,1,1,1/(IF(CountCol_range_before_split=1,1,CountCol_range_before_split-1))),0)>1,separator_row,"|"),0,range_before_split))
L34:L36	L34	=LET(range1,A2#,range_to_concatenate,B2#,col_range_to_concat,B:B,range2,D2#,separator_row,"\", range_before_split,IFERROR(INDEX(col_range_to_concat,IF((range2=TRANSPOSE(range1)),TRANSPOSE(ROW(range_to_concatenate)),"")),"|"), CountCol_range_before_split,COLUMNS(range_before_split), range_to_split,TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_range_before_split,1,1,1/(IF(CountCol_range_before_split=1,1,CountCol_range_before_split-1))),0)>1,separator_row,"|"),0,range_before_split), SUBSTITUTE( TRANSPOSE(INDEX((FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN(separator_row,,range_to_split),separator_row,"</a><a>")&"</a></b>","//a")), SEQUENCE(ROWS(range_to_split), LEN(INDEX(range_to_split,1,1))-LEN(SUBSTITUTE(INDEX(range_to_split,1,1),separator_row,))+1))), "||",""))
Dynamic array formulas.