Calculate rank in time range.

OK - see if the attached works for you. The complication is where the same person surpasses their own ranking etc.

the results are really driven by the below "key":

=AGGREGATE(15,6,(COLUMN(Sheet1!$D$2:$PW$509)*10^7+(100000-Sheet1!$D$2:$PW$509)+ROW(Sheet1!$D$2:$PW$509)/1000)/(Sheet1!$D$2:$PW$509>N($B2)),1)

this derives the best ranking, as it changes over time - but in such a way as to also flag where in the matrix this value resides.

the subsequent calcs are then just used to ensure that where the same player holds the top rating over an extended period, but with different values, they are listed just once.

above not particularly efficient and assumes XL2010 or later - you state 2016.

edit: there is a typo on Sheet2!B3 --
formula should read: =INDEX(Sheet1!$A:$PW,ROUND(MOD($A3,1)*1000,0),ROUND($A3/10^7,0))

this change won't alter final output given no-one has (as yet) surpassed Sachin Tendulkar, but it will generate a few more results in Columns A:D so extend these down to row 152 (last rating being 18426).

not quite sure which bits - as mentioned in earlier note - the crucial piece is the "key" -- this is logged in Col A on Sheet2 in my prior attachment.

the "key" is generating a number for every cell in your Sheet1 matrix from D2 (Andy Flower - Jan'71) through to PW509 (Zulqarnain Aug'19)

the "key" itself is comprised of 3 values:

[1] column # * 10^7: so Col D (Column # 4) would generate a value of 4000000
[2] 100000 - score: so a score of 82 would generate a value of 99918 ; we do the subtraction from 100k to ensure that the higher the rating the lower the resulting value
[3] row number / 1000: so row number 242 would generate a decimal value of 0.242

the above, which all relate to John Edrich Jan'71 (D242), get combined to generate a single value: 40099918.242

and as noted you get one of these values for every cell in the matrix

what we then do is divide these values by a Boolean (True/False)
the Boolean in this case is whether or not the score, in the given cell within the matrix, is higher than the previous score we logged in our results table
if the answer is "no" (i.e. not greater than prior) then the "key" value is effectively voided / removed from our array of result values, as irrelevant
it is ignored because we told our AGGREGATE calc to ignore error values, and in native XL, when coerced, True = 1 and False = 0 -- and if you divide anything by 0 (False) you'll get an error

so, having applied the above we're left with an array of "key" values - all of which relate to scores that are higher than our previous "highest"

the key value is comprised in such a way that we know the key we want to return is the smallest, i.e. that which occurred in the earliest month (as driven by the column number), and which relates to the highest rating (remember: component 2 of the key is: 100000-score, so the bigger the rating the smaller the number in the key)

in the AGGREGATE we use a k value of 1 to ensure that we always pull the smallest value -- so for our first result that is: 40099918.242

the 4000000 in the above tells us the column number (4000000/1000000 = 4)
the .242 in the above tells us the row number (.242 * 1000 = 242)

we can then use the above values to identify the cell on Sheet1 that we're interested in - row 242, column 4 (D) -- and that's John Edrich.

when we copy down to the next row, we will get a result of 50099832.124

we can then use the above values to identify the cell on Sheet1 that we're interested in - row 124, column 5 (E) -- and that Dennis Amiss

Col B:C on Sheet2 are returning the Score & Name for the "key" in Col A, and Col D builds an index of Players which will only increment if the Name changes from Prior row.
The Index in Col D is then used in the results table to ensure we get one row per Player, per sequence, and we use the Key value related to that index to return the other info like URL.

Can't explain much more than that, I'm afraid... it's not a trivial calculation.

code-wise you could use something pretty trivial like the below - though others could no doubt simplify yet further.

working version embedded in the attached -- formula version on sheet2, vba output on sheet 3 (invoked via button)

Please Login or Register  to view this content.