Ranking using multiple criteria but not skipping numbers

[creamcake]

Hello excel wizards - this is my first post, so apologies in advance for anything I might state against forum rules.

I am looking to try and recreate the 4th column (col E - 'desired result') by using some fancy excel wizardy please.
The data table I have created is only a very small sample as the question numbers in reality go up to 15, and the data is over 12 months with almost 60 'people' . currently the data has reached 41000 rows.


[TABLE="width: 263"]
<tbody>[TR]
[TD="width: 64, bgcolor: #D9D9D9"]Q No.

[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Month
[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Person
[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Score

[/TD]
[TD="width: 95, bgcolor: #D9D9D9"]Desired result

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1

[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]98
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent"]91
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]99
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]99
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent"]98
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]99
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2

[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]99
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]Apr-17
[/TD]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent"]97
[/TD]
[TD="bgcolor: transparent"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]a
[/TD]
[TD="bgcolor: transparent"]97
[/TD]
[TD="bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent"]97
[/TD]
[TD="bgcolor: transparent"]2

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]May-17
[/TD]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]May-17

[/TD]
[TD="bgcolor: transparent"]d

[/TD]
[TD="bgcolor: transparent"]100
[/TD]
[TD="bgcolor: transparent"]1

[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-
If your not familiar with VBA see " Save & Run " below.

[COLOR="Navy"]Sub[/COLOR] MG26Apr50
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

Lst = Range("A" & Rows.Count).End(xlUp).Row

[COLOR="Navy"]For[/COLOR] n = 2 To Lst [COLOR="Navy"]Step[/COLOR] 4
    [COLOR="Navy"]Set[/COLOR] Rng = Cells(n, "D").Resize(4)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
        [COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
        
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Add Dn.Value: [COLOR="Navy"]Next[/COLOR]
            .Sort
            .Reverse
            Ray = .ToArray
        [COLOR="Navy"]End[/COLOR] With
c = 0
        [COLOR="Navy"]For[/COLOR] Rw = 0 To UBound(Ray)
            [COLOR="Navy"]If[/COLOR] Not .exists(Ray(Rw)) [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                .Item(Ray(Rw)) = .Count + 1
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Rw
    
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Dn.Offset(, 1) = .Item(Dn.Value)
        [COLOR="Navy"]Next[/COLOR] Dn
        [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

To Save and Run Code:-

Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.

Regards Mick