UNIQUE values in matrix formula

[FreddieITA91]

Hi all,

I have a Gaant Chart where i set daily activities details.
In C column for each row have formula that collect weeknum from row1, corresponding every no-empty cell for that row.

matrix formula in Cx:

=TEXTJOIN(", ", TRUE, IF(F4:AAE4<>"", F$1:AAE$1,""))

As you see, this matrix formula collect all values, even duplicates.

I should collect unique values, but i have not UNIQUE function on my excel version. How can i solve this?

for example, in C4 expected "WK#22, WK#23" only values

 

[Phuoc]

Try this:

Thong tin xuat du lieu.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		ww1	ww1	ww1	ww1	ww1	ww2	ww2	ww2	ww2	ww2	ww3	ww3	ww3	ww3	ww3	ww4	ww4
2
3
4	ww1, ww2, ww3		1	1			2	2	2				3	3	3
Sheet1

Cell Formulas
Range	Formula
E4	E4	=TEXTJOIN(", ",TRUE,IF(COUNTIFS(F4:AAE4,"<>",F$1:AAE$1,IF(F$1:AAE$1<>$G$1:$AF$1,F$1:AAE$1,""))>0,F$1:AAE$1,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

but i have not UNIQUE function on my excel version

Which version are you using? You have listed 5 different versions & none of them have the Textjoin function.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[FreddieITA91]

Which version are you using? You have listed 5 different versions & none of them have the Textjoin function.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You re right, personal detail has been updated.

Try this:

Thong tin xuat du lieu.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		ww1	ww1	ww1	ww1	ww1	ww2	ww2	ww2	ww2	ww2	ww3	ww3	ww3	ww3	ww3	ww4	ww4
2
3
4	ww1, ww2, ww3		1	1			2	2	2				3	3	3
Sheet1

Cell Formulas
Range	Formula
E4	E4	=TEXTJOIN(", ",TRUE,IF(COUNTIFS(F4:AAE4,"<>",F$1:AAE$1,IF(F$1:AAE$1<>$G$1:$AF$1,F$1:AAE$1,""))>0,F$1:AAE$1,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Works perfectly!! Thanks