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

 

[Vasanth88ece]

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).

Will try n come back. Thanks for yur time

 

[Peter_SSs]

Please update your profile (click your user name at the top right of the forum, then ‘Account details’) since your post #9 image clearly shows that you are not using Microsoft 365 which your profile currently shows. (Don’t forget to scroll down & ‘Save’)

When asking questions that involve filtering, you need to make sure that it is clear whether you are talking about the worksheet function FILTER or the Excel feature AutoFilter.
In this case I think that you mean AutoFilter, as evidenced by the recent part of the thread. If so, see if this does what you want without a helper column.

25 03 09.xlsm
	A	B	C	D
1	S.NO	EMP ID	NAME	TRADE
2	1	ATES	RAKESH KUMAR YADAV SHAMBHU YADAV	ARC WELDER
3	2	ATES	DURGESH KUSHWAHA RAMAKANT KUSHWAHA	ARC WELDER
4	3	ATES	ATUL KUMAR SINGH SURESH SINGH	ARC WELDER
5	4	ATES	RIYAJUDDIN ANSARI MOHAMMAD ANSARI	ARC WELDER
6	5	ATES	RAJA BABU TAHAUWAR HUSSAIN	ARC WELDER
7	6	ZTM	MUHAMMAD TUFAIL MUHAMMAD ISLAM	ELECTRICIAN
8	7	ASBC	SUMAN CHAUDHARY	ELECTRICIAN
9	8	NTM	HABIBULLAH ABDUL AZIZ	ELECTRICIAN
10	9	MSH	MD SHAKIL MD MIJANUR RAHMAN	ELECTRICIAN
11	10	ATS	RAJKUMAR YADAV LUTAWAN YADAV	ELECTRICIAN
12	11	ALB	MD NUR ALAM MUNSHI TURAF ALI MUNSHI	ELECTRICIAN
13	12	ASBC	PRASANT CHAUDHARY	ELECTRICIAN
14	13	AAA	MD WALL EKRAM MD ABDUL MOTALEB	ELECTRICIAN
15
16
17
18				Unique Trade
19				ARC WELDER
20				ELECTRICIAN
AF

Cell Formulas
Range	Formula
D19:D20	D19	=UNIQUE(FILTER(D2:D14,SUBTOTAL(103,OFFSET(D1,SEQUENCE(ROWS(D2:D14)),0,1)),""))
Dynamic array formulas.

After an AutoFilter is applied ..

25 03 09.xlsm
	A	B	C	D
1	S.NO	EMP ID	NAME	TRADE
2	1	ATES	RAKESH KUMAR YADAV SHAMBHU YADAV	ARC WELDER
3	2	ATES	DURGESH KUSHWAHA RAMAKANT KUSHWAHA	ARC WELDER
4	3	ATES	ATUL KUMAR SINGH SURESH SINGH	ARC WELDER
5	4	ATES	RIYAJUDDIN ANSARI MOHAMMAD ANSARI	ARC WELDER
6	5	ATES	RAJA BABU TAHAUWAR HUSSAIN	ARC WELDER
15
16
17
18				Unique Trade
19				ARC WELDER
20
AF

Cell Formulas
Range	Formula
D19	D19	=UNIQUE(FILTER(D2:D14,SUBTOTAL(103,OFFSET(D1,SEQUENCE(ROWS(D2:D14)),0,1)),""))