i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

The formula =RANK(I15,$I$15:$I$314,0) is copied down G15 to G314. I want it
to rank the data in I15:I314 from 1 to 314 without returning a duplicate rank
value even if the data in column I has matches in it.



"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Yes it does it replicates what was in your first post so I ask again change
the question marks to what you want to see on your worklsheet

123 ?
134 ?
143 ?
134 ?
111 ?

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

sorry mike, i don't think i'm explaining this well. Think i have got brain
freeze from this spreadsheet lol. I've changed the question marks in your
post to reflect the results i want.
as you can see 134 shows twice in the list so instead of returning the same
rank (which would have been 2 for both figures) i want it to show 2 and 3.

"Mike H" wrote:

Yes it does it replicates what was in your first post so I ask again change
the question marks to what you want to see on your worklsheet

123 4
134 2
143 1
134 3
111 5

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

I think we have a solution!! Put this in a cell and commit with
Ctrl+Shift+Enter and Excel will put curly brackets around it { } because
it's an array. Drag it down

=SUM(1*(I15<$I$15:$I$314))+1+IF(ROW(I15)-ROW($I$15)=0,0,SUM(1*(I15=OFFSET($I$15,0,0,INDEX(R OW(I15)-ROW($I$15)+1,1)-1,1))))

Remember its an array so Ctrl+Shift+Enter

Mike

"Charno" wrote:

sorry mike, i don't think i'm explaining this well. Think i have got brain
freeze from this spreadsheet lol. I've changed the question marks in your
post to reflect the results i want.
as you can see 134 shows twice in the list so instead of returning the same
rank (which would have been 2 for both figures) i want it to show 2 and 3.

"Mike H" wrote:

Yes it does it replicates what was in your first post so I ask again change
the question marks to what you want to see on your worklsheet

123 4
134 2
143 1
134 3
111 5

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Hi Charno,

You could insert a helper column J
Then put this in J15
=I15-ROW()/10^10
and drag it down to the end of your data

Then your rank formula in K would be
=RANK(J15,$J$15:$J$314,0)

You can then hide your helper column J.

This will return in your example the first occurence
of 134 as 2 and the second as 3.

HTH
Martin




"Charno" wrote in message
...
The formula =RANK(I15,$I$15:$I$314,0) is copied down G15 to G314. I want
it
to rank the data in I15:I314 from 1 to 314 without returning a duplicate
rank
value even if the data in column I has matches in it.



"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Thats done it thank you,

Charno

"Mike H" wrote:

I think we have a solution!! Put this in a cell and commit with
Ctrl+Shift+Enter and Excel will put curly brackets around it { } because
it's an array. Drag it down

=SUM(1*(I15<$I$15:$I$314))+1+IF(ROW(I15)-ROW($I$15)=0,0,SUM(1*(I15=OFFSET($I$15,0,0,INDEX(R OW(I15)-ROW($I$15)+1,1)-1,1))))

Remember its an array so Ctrl+Shift+Enter

Mike

"Charno" wrote:

sorry mike, i don't think i'm explaining this well. Think i have got brain
freeze from this spreadsheet lol. I've changed the question marks in your
post to reflect the results i want.
as you can see 134 shows twice in the list so instead of returning the same
rank (which would have been 2 for both figures) i want it to show 2 and 3.

"Mike H" wrote:

Yes it does it replicates what was in your first post so I ask again change
the question marks to what you want to see on your worklsheet

123 4
134 2
143 1
134 3
111 5

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Your very welcome and thanks for the feedback

"Charno" wrote:

Thats done it thank you,

Charno

"Mike H" wrote:

I think we have a solution!! Put this in a cell and commit with
Ctrl+Shift+Enter and Excel will put curly brackets around it { } because
it's an array. Drag it down

=SUM(1*(I15<$I$15:$I$314))+1+IF(ROW(I15)-ROW($I$15)=0,0,SUM(1*(I15=OFFSET($I$15,0,0,INDEX(R OW(I15)-ROW($I$15)+1,1)-1,1))))

Remember its an array so Ctrl+Shift+Enter

Mike

"Charno" wrote:

sorry mike, i don't think i'm explaining this well. Think i have got brain
freeze from this spreadsheet lol. I've changed the question marks in your
post to reflect the results i want.
as you can see 134 shows twice in the list so instead of returning the same
rank (which would have been 2 for both figures) i want it to show 2 and 3.

"Mike H" wrote:

Yes it does it replicates what was in your first post so I ask again change
the question marks to what you want to see on your worklsheet

123 4
134 2
143 1
134 3
111 5

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?

Try this:

=RANK(I15,I$15:I$314)+COUNTIF(I$15:I15,I15)-1

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
I think we have a solution!! Put this in a cell and commit with
Ctrl+Shift+Enter and Excel will put curly brackets around it { } because
it's an array. Drag it down

=SUM(1*(I15<$I$15:$I$314))+1+IF(ROW(I15)-ROW($I$15)=0,0,SUM(1*(I15=OFFSET($I$15,0,0,INDEX(R OW(I15)-ROW($I$15)+1,1)-1,1))))

Remember its an array so Ctrl+Shift+Enter

Mike

"Charno" wrote:

sorry mike, i don't think i'm explaining this well. Think i have got
brain
freeze from this spreadsheet lol. I've changed the question marks in your
post to reflect the results i want.
as you can see 134 shows twice in the list so instead of returning the
same
rank (which would have been 2 for both figures) i want it to show 2 and
3.

"Mike H" wrote:

Yes it does it replicates what was in your first post so I ask again
change
the question marks to what you want to see on your worklsheet

123 4
134 2
143 1
134 3
111 5

Mike


"Charno" wrote:

it still returns duplicate numbers if there is matching data in
column I.

"Mike H" wrote:

Ah I think I see, rank wouldn't produce the result in your example
so perhaps
that's what you want to see so try this.

=SUMPRODUCT(--(I15<$I$15:$I$314),1/COUNTIF($I$15:$I$314,$I$15:$I$314&""))+1

Mike

"Mike H" wrote:

For your posted example what would you want to see as a result?

"Charno" wrote:

i have the following formula to rank a column of data
=RANK(I15,$I$15:$I$314,0) but the problem i have is that if the
data is
duplicated then it returns a duplicate rank. ie..

DATA Rank
123 3
134 2
143 1
134 2
111 4

how do i get the formula to no duplicate the rank?