TextJoin without duplicates

[Shwapx]

So I'm having a formula which is TextJoining headers and outputting them in one cell, but based on the data I might end up with duplicates.

The formula which I have now it's looking In for example cell A1 if there is "Code1" then it's looking under cell A2 in another sheet and there is "Code1" it's outputting the header of that second sheet which is located in A1, but the real problem is when you have "Code1" in A3 as well then you see the header twice. I have tried with unique or match, but no luck.

This is example of my formula - =IF(AND(E2<>"",COUNTA(Sheet2!$D$1:$BS$1)=COLUMNS(Sheet2!D1:BS1)),TEXTJOIN(",",,IF(E2=Sheet2!$D$2:$BS$428,Sheet2!$D$1:$BS$1,"")),"")

Thanks in advance.

 

[kweaver]

Could you post a sample of your sheet using XL2BB and your expect result(s)?

 

[Shwapx]

Hello @kweaver here is the XL2BB really simple table, but represent the issue, on my case the codes which I'm looking are located in Sheet2, but that can be adjusted in the formula.

This is the formula which I'm using in G5 to collect the result which I'm getting now - =IF(AND(E2<>"",COUNTA(A1:C1)=COLUMNS(A1:C1)),TEXTJOIN(" / ",,IF(E2=A2:C4,A1:C1,"")),"")
I'm looking to adjust this so the result looks more like cell G2.

Book1
	A	B	C	D	E	F	G
1	Header1	Header2	Header3		Codes to check		Expected result
2	Code1	Code2	Code1		Code1		Header1 / Header2 / Header3
3	Code3	Code1	Code2		Code2
4	Code1				Code3		Result which I'm having now
5							Header1 / Header2 / Header3 / Header 1
6
7
8					I'm getting double the Header1 from the textjoin because I'm having 2 times Code1 in the column A which is under header1, Is there a way to collect only unuqie values by basically count same codes as 1?
Sheet1

 

[Peter_SSs]

Try this

23 06 21.xlsm
	A	B	C	D	E	F	G
1	Header1	Header2	Header3		Codes to check		Expected result
2	Code1	Code2	Code1		Code1		Header1 / Header2 / Header3
3	Code3	Code1	Code2		Code2
4	Code1				Code3
Unique Headers

Cell Formulas
Range	Formula
G2	G2	=LET(h,TOCOL(IF(ISNUMBER(MATCH(A2:C4,E:E,0)),A1:C1,"")),TEXTJOIN(" / ",,UNIQUE(FILTER(h,h<>""))))

 

[Shwapx]

Thanks @Peter_SSs I think it's working, but how I collect the next row in G3 it should be printing Header2 / Header3 since we have Code2 only in these headers.

 

[Peter_SSs]

but how I collect the next row in G3 it should be printing Header2 / Header3 since we have Code2 only in these headers.

I completely misinterpreted your requirement because your original post showed only one "expected result".
Try this instead

23 06 21.xlsm
	A	B	C	D	E	F	G
1	Header1	Header2	Header3		Codes to check		Expected result
2	Code1	Code2	Code1		Code1		Header1 / Header3 / Header2
3	Code3	Code1	Code2		Code2		Header2 / Header3
4	Code1				Code3		Header1
Unique Headers (2)

Cell Formulas
Range	Formula
G2:G4	G2	=LET(h,TOCOL(IF(A$2:C$4=E2,A$1:C$1,"")),TEXTJOIN(" / ",,UNIQUE(FILTER(h,h<>""))))

 

[Georgiboy]

This should have been all in the one thread, there was no need for two.

 

[Shwapx]

I completely misinterpreted your requirement because your original post showed only one "expected result".
Try this instead

23 06 21.xlsm
	A	B	C	D	E	F	G
1	Header1	Header2	Header3		Codes to check		Expected result
2	Code1	Code2	Code1		Code1		Header1 / Header3 / Header2
3	Code3	Code1	Code2		Code2		Header2 / Header3
4	Code1				Code3		Header1
Unique Headers (2)

Cell Formulas
Range	Formula
G2:G4	G2	=LET(h,TOCOL(IF(A$2:C$4=E2,A$1:C$1,"")),TEXTJOIN(" / ",,UNIQUE(FILTER(h,h<>""))))

Thank you it's working as intended!

 

[Shwapx]

This should have been all in the one thread, there was no need for two.

Yes I think the previous thread was quite old that's why I have created a new one.

 

[Peter_SSs]

Yes I think the previous thread was quite old that's why I have created a new one.

That is fair enough, but you should not have started a new one and revived the old one so that there were two threads with basically the same question as per #12 of the Forum Rules

Thank you it's working as intended!

You are welcome. Thanks for the confirmation.