Ranking different groups in one column

[Marie Bayes]

Hi
I have three columns, column A contains a 'region' name, column B, has the
store name and column C has a 'score'. I want to rank the stores (B) within
the region they're in (A) based on the score (C) and return the result into 1
column (D), is there a way to use the RANK formula so that I get each store's
rank within their region returned into the one column (D)?
Thanks in advance

 

[Gary''s Student]

Sort the three columns by region and score. The store with the highest score
will appear first in the set of data for any region. Then just use VLOOKUP()
to get the store name.

 

[Marie Bayes]

Thanks for your response GS, however, this won't solve my problem. I cannot
sort the data in the spreadsheet as I'm setting up a crystal report for
a.n.other user from this datasheet and just 'pulling' the rank no into the
report (so just the name of the top rank won't do), so there can be no
sorting as the end user won't see the original data (the data is subject to
change weekly and is just 'dumped' into the spreadsheet). I need to just
pull in the ranked no.

Therefore, back to my original question if that's ok, do you (or anyone)
know of a way to use the ranking formula to do this?

 

[Ragdyer]

If I understand your question, the actual name of the store is incidental.

You're looking for the rank within the regions in Column A.

Say your datalist is in A1 to C20.

Enter this formula in D1, and copy down:

=SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1

 

[Gary''s Student]

I have a solution for you, but its not very clean. Here is some sample data:

reg3 store25 423
reg2 store15 477
reg2 store18 106
reg1 store2 456
reg2 store19 462
reg1 store1 104
reg3 store26 474
reg3 store28 27
reg1 store10 361
reg1 store7 36
reg1 store8 88
reg2 store11 22
reg2 store16 35
reg2 store20 25
reg3 store22 378
reg2 store14 390
reg3 store30 8
reg3 store29 17
reg1 store3 402
reg2 store17 15
reg1 store6 59
reg1 store5 245
reg3 store23 21
reg1 store9 341
reg3 store27 439
reg2 store12 125
reg2 store13 118
reg1 store4 12
reg3 store21 33
reg3 store24 4

As you see, three regions, a bunch of store names and scores. The first
step is to find the highest score for a given region. In C1 enter the array
formula:
=MAX(IF(A1:A30="reg1",C1:C30))

This must be entered with a CNTRL-SHFT-ENTER rather than just the ENTER key.

C1 displays 456

At this point we can try MATCH & INDEX, but the 456 may appear several
times, so we must find the 456 for reg1 only. In E1 we enter:

=SUMPRODUCT(--(A1:A30="reg1"),--(C1:C30=D1),(ROW(1:30)))

This displays 4. So we want the fourth row. Finally in F1 we enter:

=INDEX(B:B,E1)

which displays:

store2

You can use this until some gives you a more compact solution.

 

[Marie Bayes]

Hi Ragdyer
I liked this response as it was less complicated than Gary's student's
response, however, it returns the ranking of 1 in every cell, maybe it needs
a small adjustment...?

 

[Marie Bayes]

Thanks for your response, it is complicated, it looks like it'll work, but
I'm just waiting for, as you put it, a more compact solution (if one exists),
if that's not forthcoming then I will take you up on this one!! Thanks for
all the effort with this one though.

 

[Ragdyer]

Check to make sure your actual ranges match the ranges in the formula you're
using.

This works perfectly on the 30 row example that Gary posted:

=SUMPRODUCT(($A$1:$A$30=A1)*(C1<$C$1:$C$30))+1

 

[Marie Bayes]

You're right, I had typed part of it incorrectly (apologies), however, if you
look at the sample results below you'll see that it's returning the first
store, Colchester, which has 0% as ranked first along with the two 'true'
rank 1 positions. Do you know why this would be (I've checked that the cell
for Central & East is identical to those below (by copying from the one below
it)?

Central & East Colchester 0.0% 1
Central & East Coventry 1.6% 13
Central & East Birmingham 26.2% 12
Central & East Ipswich 26.2% 10
Central & East Norwich 34.5% 10
Central & East Peterborough 45.2% 9
Central & East Hemel Hempstead 59.5% 8
Central & East Stevenage 63.1% 7
Central & East Cambridge 71.4% 6
Central & East Braintree 94.0% 5
Central & East Milton Keynes 97.6% 4
Central & East Harlow 97.6% 1
Central & East Northampton 97.6% 1

 

[Ragdyer]

Upon examining your posted results, it's evident that your percentage column
is *not* displaying the actual value in the cell, but probably the
"formatted" display value.

Are there formulas in Column C, returning the percentage values you posted?

Your last 3 entries display the exact same percents, but, as you can see,
the rankings are not equal.
This means the *actual, true* value in the cell, the value that XL uses for
calculating, is being masked by formatting.

If you try the formula on Gary's example, and just substitute a zero value
for any value in the datalist, you'll see that a *true* zero will rank last.

I would venture to say that your skewed results are being caused by the
manner in which you're populating the percentage column.

Perhaps you should try wrapping your percent formulas with the Round()
function instead of just formatting the cells.

BTW, a <space> in a percent column cell will return a rank of 1.

 

[Marie Bayes]

Thanks for getting back and another apology, another mistake in the formula,
I'd had it checking row 1 when on row 2 (I forgot about my header row) etc
etc, I've fixed this and now the zero value is showing in the right place,
thanks once again and sorry to have made such a stupid mistake!

 

[Ragdyer]

Glad you got it all working correctly.

And thank you for feeding back.

 

[Ditch]

Great tip Tam.
Anyway of breaking the ties?

If I understand your question, the actual name of the store is incidental.

You're looking for the rank within the regions in Column A.

Say your datalist is in A1 to C20.

Enter this formula in D1, and copy down:

=SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1

 

[Ditch]

Team,
Thank you for the ranking different groups within one colmun formula:
={SUMPRODUCT(($A$1:$A$6000=$A7)*($B1<$B$1:$B$6000))+1}
array formula <control-shift-enter>

To break deadlocks I set up an adjacent column C & use this formula that I
found elsewhere on this forum:
=B1-ROW()/10^10 which gives every value in column B a unique value
then the ranking column becomes
{=SUMPRODUCT(($A$1:$A$6000=$A7)*($C1<$C$1:$C$6000))+1}

the problem I'm now having is that it only works up to ~4,000 rows and I
need to go to 10,000 and the Rank formula then returns #N/A
It seems to be very temperamental.

Any tips would be welcome.

ta
Ditch