Excel Formula Text Twist

[Biz]

Dear All,

I'm trying to match multiple criteria and return comments using MS Excel 2021.

I have tried composing a formula in Column I, but my answers don't equal to should be Column J.

Tester 50123 v2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Parent	Child	Amount	Comments			Criteria 1	Criteria 2	Cons Comments	Should Be
2	A	123	10				A	123	B-123-Try, B-123-Bob, B-123-Ken, D-200-This, D-200-is, D-200-Working
3	B	123	20	Try			B	123	B-123-Try, B-123-Bob, B-123-Ken, D-200-This, D-200-is, D-200-Working	B-123-Try, B-123-Bob, B-123-Ken, D-200-This, D-200-is, D-200-Working
4	A	123	10				C	120	B-123-Try, B-123-Bob, B-123-Ken, D-200-This, D-200-is, D-200-Working
5	B	123	20				D	200	B-123-Try, B-123-Bob, B-123-Ken, D-200-This, D-200-is, D-200-Working
6	B	123	20	Bob
7	B	123	20	Bob
8	B	123	20	Ken
9	C	120
10	D	200	1	This
11	D	200	2	is
12	D	200	3	Working
Sheet1

Cell Formulas
Range	Formula
J3,I3:I5	J3	=TEXTJOIN(","&CHAR(10),TRUE,UNIQUE(FILTER(($A$2:$A$20&"-"&$B$2:$B$20&"-"&$D$2:$D$20),($D$2:$D$20<>"")+($A$2:$A$20&"- "&$B$2:$B$20=H3&"-"&I3))))
I2	I2	=TEXTJOIN(","&CHAR(10),TRUE,UNIQUE(FILTER(($A$2:$A$20&"-"&$B$2:$B$20&"-"&$D$2:$D$20),($D$2:$D$20<>"")+($A$2:$A$20=G2&"- "&$B$2:$B$20=H2))))

Your help would be greatly appreciated.

Kind Regards

Biz

 

[Fluff]

Do you need to update your profile as it's showing 2010, but those functions are not available in 2010?

Also can you please explain exactly what you are trying to do.

 

[Biz]

Hi Fluff,
I have updated Office Version to 2021. I'm trying to match multiple criteria in Columns G&H to Columns A&B and then return comments from Column D with Parent-Child using TextJoin with ","&CHAR(10). Hope it makes sense.

Kind Regards,

Biz.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Parent	Child	Amount	Comments			Criteria 1	Criteria 2	Cons Comments
2	A	123	10				A	123
3	B	123	20	Try			B	123	B-123-Try, B-123-Bob, B-123-Ken
4	A	123	10				C	120
5	B	123	20				D	200	D-200-This, D-200-is, D-200-Working
6	B	123	20	Bob
7	B	123	20	Bob
8	B	123	20	Ken
9	C	120
10	D	200	1	This
11	D	200	2	is
12	D	200	3	Working
13
Main

Cell Formulas
Range	Formula
I2:I5	I2	=TEXTJOIN(","&CHAR(10),TRUE,UNIQUE(FILTER($A$2:$A$20&"-"&$B$2:$B$20&"-"&$D$2:$D$20,($D$2:$D$20<>"")*($A$2:$A$20=G2)*($B$2:$B$20=H2),"")))

 

[Biz]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Parent	Child	Amount	Comments			Criteria 1	Criteria 2	Cons Comments
2	A	123	10				A	123
3	B	123	20	Try			B	123	B-123-Try, B-123-Bob, B-123-Ken
4	A	123	10				C	120
5	B	123	20				D	200	D-200-This, D-200-is, D-200-Working
6	B	123	20	Bob
7	B	123	20	Bob
8	B	123	20	Ken
9	C	120
10	D	200	1	This
11	D	200	2	is
12	D	200	3	Working
13
Main

Cell Formulas
Range	Formula
I2:I5	I2	=TEXTJOIN(","&CHAR(10),TRUE,UNIQUE(FILTER($A$2:$A$20&"-"&$B$2:$B$20&"-"&$D$2:$D$20,($D$2:$D$20<>"")*($A$2:$A$20=G2)*($B$2:$B$20=H2),"")))

Thank you Fluffy for your help.

 

[Fluff]

You're welcome & thanks for the feedback.