Equal ranks - but no gaps
 

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

It's late ...ignore my last reply!

"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

Try:

=SMALL($A$1:$A$5,ROW())

"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

one method would be to use some helper columns(C:D?)

use advanced filter to generate a list of unique numbers from column A in
the first helper column
in the second helper column, use the rank() function on the first helper
column
in column B use vlookup(A2,C$1!:D$3,2) copy and drag down


"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

Set B2=1

in B3 and copy down:

=IF(A3=A2,B2,B2+1)



"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

If the values are unsorted:

=IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Set B2=1

in B3 and copy down:

=IF(A3=A2,B2,B2+1)



"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

Thanks a lot!! Worked like a charm!!!!!

"T. Valko" wrote:

If the values are unsorted:

=IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Set B2=1

in B3 and copy down:

=IF(A3=A2,B2,B2+1)



"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas

Equal ranks - but no gaps
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Thomas" wrote in message
...
Thanks a lot!! Worked like a charm!!!!!

"T. Valko" wrote:

If the values are unsorted:

=IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Set B2=1

in B3 and copy down:

=IF(A3=A2,B2,B2+1)



"Thomas" wrote:

I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I
would
like to see it):

A B
1 Value Rank
2 1 1
3 1 1
4 1 1
5 2 2 <----- that being the 'tricky' part :)
6 3 3

Thank you very much!
Thomas