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

"Marie Bayes" wrote:

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.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200776


"Marie Bayes" wrote:

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?

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200776


"Marie Bayes" wrote:

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 :)