Unique function applied to the filter values

[Vasanth88ece]

Hey Guys,

I wanna use unique function for the range where the filter is applied. I have applied unique function in value range. When I filter "a" from filter range I ll get the filtered value like ss 2 times , ed 1 time and dd 2 times. all i need is to get the unique from that range. Not to the whole range.

Filter	value
a	ss
a	ss
a	ed
a	dd
a	dd
f	ch
f	ch
f	vb
f	vb
f	de
f	de

 

[hagia_sofia]

Hello, do you mean:

=UNIQUE(FILTER(B2:B12,A2:A12="a"))

 

[Vasanth88ece]

Hello, do you mean:

=UNIQUE(FILTER(B2:B12,A2:A12="a"))

Not exactly.

S.NO	EMP ID	NAME	TRADE
1	ATES	RAKESH KUMAR YADAV SHAMBHU YADAV	ARC WELDER
2	ATES	DURGESH KUSHWAHA RAMAKANT KUSHWAHA	ARC WELDER
3	ATES	ATUL KUMAR SINGH SURESH SINGH	ARC WELDER
4	ATES	RIYAJUDDIN ANSARI MOHAMMAD ANSARI	ARC WELDER
5	ATES	RAJA BABU TAHAUWAR HUSSAIN	ARC WELDER
6	ZTM	MUHAMMAD TUFAIL MUHAMMAD ISLAM	ELECTRICIAN
7	ASBC	SUMAN CHAUDHARY	ELECTRICIAN
8	NTM	HABIBULLAH ABDUL AZIZ	ELECTRICIAN
9	MSH	MD SHAKIL MD MIJANUR RAHMAN	ELECTRICIAN
10	ATS	RAJKUMAR YADAV LUTAWAN YADAV	ELECTRICIAN
11	ALB	MD NUR ALAM MUNSHI TURAF ALI MUNSHI	ELECTRICIAN
12	ASBC	PRASANT CHAUDHARY	ELECTRICIAN
13	AAA	MD WALL EKRAM MD ABDUL MOTALEB	ELECTRICIAN

When I apply filter in EMP ID I want the unique values of TRADE column. Whenever Filter is applied I should get the unique value of Trade in different cell. Forget the filter applying procedure that i have as list in different cell. All I need is when filter is applied I should get the unique value of Trade in different cell.

 

[hagia_sofia]

Many thanks for the reply - although I am not sure whether I understand it correctly:

=UNIQUE(FILTER(D2:D14,BYROW(B2:B14,LAMBDA(x,SUBTOTAL(3,x)))))

 

[Vasanth88ece]

Many thanks for the reply - although I am not sure whether I understand it correctly:

=UNIQUE(FILTER(D2:D14,BYROW(B2:B14,LAMBDA(x,SUBTOTAL(3,x)))))

Will put it another way. I want unique values from TRADE column at any point of filter applied to any other columns. I want it to get the unique values that are displayed in TRADE column. Don't not include any filter func to that since I m applying it manually.

 

[hagia_sofia]

Ok, the following is the formula adjusted for manual filter applied to any column (for checking what it does cf. the picture):

=UNIQUE(FILTER(D2:D14,BYROW(A2:D14,LAMBDA(x,SUBTOTAL(3,x)))))

If it is not the desired output, could you please post a picture how it should look like?

 

[Vasanth88ece]

Ok, the following is the formula adjusted for manual filter applied to any column (for checking what it does cf. the picture):

=UNIQUE(FILTER(D2:D14,BYROW(A2:D14,LAMBDA(x,SUBTOTAL(3,x)))))

If it is not the desired output, could you please post a picture how it should look like?

S.NO	EMP ID	NAME	TRADE	#NAME?
1	ATES	RAKESH KUMAR YADAV SHAMBHU YADAV	ARC WELDER
2	ATES	DURGESH KUSHWAHA RAMAKANT KUSHWAHA	ARC WELDER
3	ATES	ATUL KUMAR SINGH SURESH SINGH	ARC WELDER
4	ATES	RIYAJUDDIN ANSARI MOHAMMAD ANSARI	ARC WELDER
5	ATES	RAJA BABU TAHAUWAR HUSSAIN	ARC WELDER

It is throwing me a #Name error. Help me on this. Ur image is the right thing that I m looking for

 

[hagia_sofia]

If you really are using Excel 365, then you could maybe try to update your Office?

 

[Vasanth88ece]

If you really are using Excel 365, then you could maybe try to update your Office?

 

[hagia_sofia]

As Excel 2021 does not have BYROW, as an alternative, if it is possible for you to add another column, you could try:

1) use a helper column with the following formula insterted in E2 and copied down:

=AGGREGATE(3,5,A2)

2) then use the following formula to filter:

=LET(
a,D2:D14,
f,E2:E14,
r,SUM(f)=ROWS(f),
IF(r,"no filter applied",UNIQUE(FILTER(a,f))))

where E2:E14 represents the column where there is the formula ad 1).