If greater or less than unique vaules

[Fin Fang Foom]

Hi everyone,


I have this formula below it will retrieve unique vaules and sort them in a Ascending order.

=INDEX($A$2:$A$1000,MATCH(SUM(COUNTIF($A$2:$A$1000,C$1:C1)),COUNTIF($A$2:$A$1000,"<"&$A$2:$A$1000)+ISTEXT($A$2:$A$1000)*SUM(--ISNUMBER($A$2:$A$1000))-ISBLANK($A$2:$A$1000),0))


But I would like to add a condition in the formula. If greater or equal to 3 and Less or equal to 9.


I tried to modifed it but no luck.

=INDEX($A$2:$A$1000,MATCH(SUM(COUNTIF($A$2:$A$1000,C$1:C1)),IF($A$2:$A$1000>=3,IF(A$2:A$1000<=9,COUNTIF($A$2:$A$1000,"<"&$A$2:$A$1000)+ISTEXT($A$2:$A$1000)*SUM(--ISNUMBER($A$2:$A$1000))-ISBLANK($A$2:$A$1000),0))))


The expected results I'm looking for are in column E

Book3
	A	B	C	D	E	F
1	REASON#				Expected Results
2	1		1		3
3	2		2		5
4	3		3		6
5	10		4		7
6	5		5		8
7	6		6		9
8	7		7
9	8		8
10	9		9
11	10		10
12	11		11
13	12		12
14	3		14
15	14		15
16	15		18
17	8		19
18	7		20
19	18		21
20	19		22
Sheet1

 

[Jon von der Heyden]

I've done it in two stages:

Book3
	A	B	C	D
1	No #	Unique
2	7	7		3
3	11	11		4
4	17	17		5
5	7	0		6
6	3	3		7
7	11	0		8
8	18	18		#NUM!
9	4	4		#NUM!
10	12	12
11	20	20
12	6	6
13	8	8
14	11	0
15	16	16
16	7	0
17	5	5
18	18	0
19	15	15
20	16	0
21	14	14
22	11	0
Sheet1

In column B I've created a list of unique items, all duplicated values now appear as 0 (ommitted from SMALL() result)

Column D uses a SMALL(IF()) array formula: =SMALL(IF(($B$2:$B$22>=3)*($B$2:$B$22<=9),$B$2:$B$22),ROW()-1)

Hope this helps

 

[Fin Fang Foom]

Thanks Jon von der Heyden. I should've mention I'm looking for a all in one formula. Because I will incorporate the formula into a macro.

Again I tried to modified it and still it does not work.

=INDEX($A$2:$A$1000,MATCH(SUM(COUNTIF($A$2:$A$1000,F$1:F1)),IF(($A$2:$A$1000>=3)*($A$2:$A$1000<=29),COUNTIF($A$2:$A$1000,"<"&$A$2:$A$1000)+ISTEXT($A$2:$A$1000)*SUM(--ISNUMBER($A$2:$A$1000)))-ISBLANK($A$2:$A$1000),0))


I dont know what I'm missing.

 

[Fin Fang Foom]

Bump

 

[Domenic]

Since the data contains numerical values, maybe...

B2:

=COUNT(1/FREQUENCY(IF(A2:A19>=3,IF(A2:A19<=9,A2:A19)),IF(A2:A19>=3,IF(A2:A19<=9,A2:A19))))

...confirmed with CONTROL+SHIFT+ENTER

C2, copied down:

=IF(ROWS($C$2:C2)<=$B$2,MIN(IF($A$2:$A$19>=3,IF($A$2:$A$19<=9,IF(ISNA(MATCH($A$2:$A$19,$C$1:C1,0)),$A$2:$A$19)))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Fin Fang Foom]

Once again thank You for your magic!


Thank You!