rank if?

[dave]

how can I reference an array and return in a list all
nonduplicate numbers in order ascending. I could sort the
numbers and do an if statement, but I dont want to sort
them in this case. I was trying to use rank, but...


for example

1
1
3
3
5
3
4
should yield
1
3
4
5
tia,
Dave

 

[Frank Kabel]

Hi
try the following formulas (only works for numbers):
B1:
MIN($A$1:$A$100)

B2:
SMALL($A$1:$A$100,1+SUMPRODUCT(COUNTIF($B$1:$B1, $A$1:$A$100)))

and copy this formula down

 

[Aladin Akyurek]

Either run Advanced Filter on the range interest with the Unique records
only option set or invoke a formula system that extracts the desired list
like:

http://www.mrexcel.com/board2/viewtopic.php?t=105230&highlight=distinct

 

[Myrna Larson]

Hi, Frank:

Of course it works just as you wrote it. So does this variation:

=SMALL($A$1:$A$60,1+SUMPRODUCT(COUNTIF($A$1:$A60, $B$1:$B1)))

I switched the arguments in the COUNTIF portion, because, to my failing brain
<g>, it's easier to understand what's happening: i.e. you want to know how
many items from the original list have already been included in the ranking,
and add 1 to that number.

Also, you stop copying down when the formula returns an error (obviously).

 

[Frank Kabel]

Hi Myrna
[...]

=SMALL($A$1:$A$60,1+SUMPRODUCT(COUNTIF($A$1:$A60, $B$1:$B1)))
I switched the arguments in the COUNTIF portion, because, to my failing brain
<g>, it's easier to understand what's happening: i.e. you want to know how
many items from the original list have already been included in the ranking,
and add 1 to that number.
))
Also, you stop copying down when the formula returns an error

(obviously).

or use:
IF($B1<MAX($A$1:$A$100),SMALL($A$1:$A$100,1+SUMPRODUCT(COUNTIF($B$1:$B1
,$A$1:$A$100))),"")

;-)

Frank

 

[Aladin Akyurek]

I see I overlooked the sorting issue...

Either run Advanced Filter on the range interest with the Unique records
only option set or invoke a formula system that extracts the desired list
like:

http://www.mrexcel.com/board2/viewtopic.php?t=105230&highlight=distinct