Unique List with condition cell contains “1”

[faizee]

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

 

[jasonb75]

This will do it

=UNIQUE(FILTER(B:B,A:A=1,""))

 

[faizee]

This will do it

=UNIQUE(FILTER(B:B,A:A=1,""))

thanks. but unfortunately i am using version 2016 and 2019. and i guess .unique and filter formula are not available in these verson?
so what to do now?

 

[jasonb75]

Do you want the results in table order, or sorted ascending / descending?

Also, what is in column B, text or numbers?

 

[faizee]

Do you want the results in table order, or sorted ascending / descending?

Also, what is in column B, text or numbers?

any order,
but preferably if it ascending also

 

[jasonb75]

As you didn't clarify the other detail that I asked about, I am going to assume numbers given that you refer to 'values' in post 1 rather than anything that would suggest text labels.

Note that the reference D$1:D1 near the end of the formula will need changing to match your actual sheet. This should refer to the cell immediately above the first formula.

faizee.xlsx
	A	B	C	D
1	Ref	List		Results
2	3	1		1
3	1	1		2
4	2	4		3
5	1	4		4
6	3	3
7	1	2
8	1	2
9	1	4
10	2	2
11	1	1
12	2	4
13	1	3
14	3	1
15	2	2
16	2	1
17	3	4
18	3	3
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(AGGREGATE(15,6,$B$2:$B$18/($A$2:$A$18=1),1+SUM(COUNTIFS($A$2:$A$18,1,$B$2:$B$18,D$1:D1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[faizee]

S#	fruits
1	apple
1	mango
2	apple
1	apple
2	mango
3	jelly
2	jelly
3	apple
2	pinnable
3	mango
2	pinnable
2	pinnable
	S No	2		unique list of fruits, where S# is 2
	apple
	mango
	jelly
	pinnable

 

[jasonb75]

This is why I asked if it was text or numbers in column B, numbers are easier to work with.

I'll have another look when I get chance but it may not be for a day or so.

 

[faizee]

This is why I asked if it was text or numbers in column B, numbers are easier to work with.

I'll have another look when I get chance but it may not be for a day or so.

sure.. thank you. i will wait

 

[faizee]

This is why I asked if it was text or numbers in column B, numbers are easier to work with.

I'll have another look when I get chance but it may not be for a day or so.

please.. can you do it?