Unique List with condition cell contains “1”

[faizee]

make the unique list of table column b values, where table column A contains “1”

 

[jasonb75]

Here's a way to extract the unique list in the same order as the original table. I'm not sure that it is possible to sort them into order without the newer 365 functions.

faizee.xlsx
	A	B	C
1	S#	fruits
2	1	apple
3	1	mango
4	2	apple
5	1	apple
6	2	mango
7	3	jelly
8	2	jelly
9	3	apple
10	2	pinnable
11	3	mango
12	2	pinnable
13	2	pinnable
14
15
16		S No	2
17		apple
18		mango
19		jelly
20		pinnable
Sheet2

Cell Formulas
Range	Formula
B17:B20	B17	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($A$2:$A$13)/($A$2:$A$13=$C$16)/ISNA(MATCH($B$2:$B$13,$B$16:$B16,0)),1)),"")

 

[jasonb75]

Following up on my reply above, I should have said that I'm not sure it is possible using a single formula which was what I was trying to do. If you're happy to use a helper column then you could do it with this method.

faizee.xlsx
	A	B	C
1	S#	fruits	Rank
2	1	apple
3	1	mango
4	2	apple	6
5	1	apple
6	2	mango	4
7	3	jelly
8	2	jelly	5
9	3	apple
10	2	pinnable	3
11	3	mango
12	2	pinnable
13	2	pinnable
14
15
16		S No	2
17		apple
18		apple
19		jelly
20		mango
Sheet2

Cell Formulas
Range	Formula
C2:C13	C2	=IF(A2=C$16,IF(COUNTIFS(A$2:A2,C$16,B$2:B2,B2)=1,COUNTIFS(A$2:A$13,A2,B$2:B$13,">="&B2),""),"")
B17:B20	B17	=IFERROR(INDEX(B:B,MATCH(SMALL($C$2:$C$13,ROWS(B$17:B17)),$C$2:$C$13,0)),"")

 

[faizee]

i hope, someone could help me out in this query

 

[jasonb75]

Are my last two replies invisible?

 

[faizee]

thank you so much