Rank values with complex rules?

rinconpaul · Sep 8th 2017

In col A subsets of 'Races" are shown. In col B subsets of Horses within each race. The number of which varies from 3 to 24. In col C values left behind in time order after removing duplicates. The most recent value is at the top of each Horses subset. In col D shows the last/most recent value.

In col E the values in col C are ranked against each other, but this is where things get complex. Take cell E2. The value at C2 is ranked against itself, C5,C10,C15,C17,C23. This rank formula is repeated progressing down col E and ranking against the next consecutive value for each horse. However because the number of values for each horse varies, you run out of consecutive values, and in this case the formula uses the last value for each horse.
e.g. Take cell E4. It's ranked against itself, E7, E12,E16* (last value for horse4),E19,E25

The rank formulas are resident in col E, so you can follow what's going on. I've shown 3 races in this example, but in reality there are many tens of thousands of races, horses and values. I should've ranked as I recorded and will in the future. It's a matter of backtracking over old data to extract more information.

From my experience solving this issue with code or formula is nigh on impossible, and anyone that can solve this would be elevated to "Genuis" status IMHO!

**Carim** · Sep 8th 2017

Re: Rank values with complex rules?

Hello,

As far as Ranking is concerned, the one person that I know, who has extensively analyzed the topic is Chip ...

http://www.cpearson.com/excel/Rank.aspx

Hope you will find some inspiration about the intricacies of this topic ... :wink:

rinconpaul · Sep 8th 2017

Re: Rank values with complex rules?

Thanks Carim for the referral. My issue is another order higher in difficulty as I'm comparing ranks with different variable length subsets and the consecutive values run out for some subsets. I know one person on here that is a Master craftsman, just got to entice him/her. In fact I'll dust off the virtual Excel Cup I issued several years ago to a forum braniac for help above and beyond and re engrave it.

**Carim** · Sep 8th 2017

Re: Rank values with complex rules?

Just LOVE .. your Incentive ... :facepull:

Personally, do not know if have enough time to devote to your extremely complex problem ...:angrypc:

P.S. You can always drop a PM to your Master craftsman ... he should, by nature, be eager to jump on your Challenge ...:jumpupdo:

Cheers

rinconpaul · Sep 8th 2017

Re: Rank values with complex rules?

OK, I'm working on this problem myself and have progressed a little more. I added a Helper column that assigns each value a count# 1,2,3... and a sumproduct function to rank. This works fine until a horses values run out compared to other horses. The formula then ranks as if that horse wasn't in the race. e.g. In Race1 the minimum number of values is two for Horse4, therefore when the sumproduct formula starts to look for value number 3's+ it leaves Horse4 out of the calculation. This worsens the further down the count of available values per horse goes.
Problems remaining:
1/ Absolute references needed for sumproduct for each race...time consuming to enter. Need a code to recognise the change in race and range of rows.
2/ An 'IF' condition needs to tell sumproduct that when you run out of value counts the last previous reference applies.

**KjBox** · Sep 9th 2017

Re: Rank values with complex rules?

I think that what you require is going to be too complex for using built-in functions and a UDF will be needed.

To make sure I understand the requirement correctly can you please attach the file again with your expected result manually entered in column I.

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

:rock:Go, you good thing!

**KjBox** · Sep 9th 2017

Re: Rank values with complex rules?

OK thanks for that.

So basically, you need result from the RANK formula used in column E to be dynamic so it allows for any number of horses in a race and any number of VALUES for each of those horses, is that correct?

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

Yes, everything is variable, races, horses, values

**KjBox** · Sep 9th 2017

Re: Rank values with complex rules?

OK I will have a play around with it and see if I can come up with a solution, got other stuff on the go too so may be a few hours before I can look into it fully.

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

No matter how great the talent, some things just take time. :drum:

**Carim** · Sep 9th 2017

Re: Rank values with complex rules?

Hello,

Yesterday, took an hour to decompose your problem ...

Attached is the initial step ... only designed to validate the understanding ...

Say for race1 ...cells J1:O10 represents the Rows to use as references ...

and cells AC1 to AH25 the rows numbers to be used for each row ...

Before jumping into the design of an UDF ... could you validate the reasoning ....

See your attached file ...

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

Carim, like a cryptologist working on the enigma code, you have validated well. Good work! Now to make it all happen, at least you have a good grasp of the intent. I will be going to bed soon in my time zone, so apologies if I don't return any posts till the morning.

**KjBox** · Sep 9th 2017

Re: Rank values with complex rules?

Here is a UDF solution.

The UDF is

Code

Option Explicit


Function RaceHorseRank(r As Range) As Integer
    Dim x, y(), z, i As Long, ii As Long, iii As Long, iv As Integer, v As Integer
    Dim rng As Range, oDicHR As Object, oDicH As Object, keeR, keeH, s As String
    
    Set oDicHR = CreateObject("scripting.dictionary"): oDicHR.comparemode = 1
    Set oDicH = CreateObject("scripting.dictionary"): oDicH.comparemode = 1
    Set rng = ActiveSheet.UsedRange
    x = rng.Resize(, 3)
    
    With oDicHR
        For i = 2 To UBound(x, 1)
            If Not .exists(x(i, 1)) Then .Add x(i, 1), Nothing
        Next
    End With
    For Each keeR In oDicHR
        If r.Offset(, -2) = keeR Then
            iv = Application.Match(keeR, rng.Columns(1), 0)
            v = iv + Application.CountIf(rng.Columns(1), keeR) - 1
            ii = 0
            With oDicH
                For i = iv To v
                    iii = 0
                    If Not .exists(x(i, 2)) Then
                        ii = i
                        Do Until x(ii, 2) <> x(i, 2)
                            iii = iii + 1: ReDim Preserve y(1 To iii): y(iii) = x(ii, 3)
                            If ii = UBound(x, 1) Then Exit Do
                            ii = ii + 1
                        Loop
                        .Add x(i, 2), y
                    End If
                Next
            End With
            s = r.Offset(, -1): y = oDicH.Item(s)
            i = Application.Match(r, y, 0)
            ii = 0
            ReDim z(1 To oDicH.Count)
            For Each keeH In oDicH.keys
                y = oDicH.Item(keeH)
                ii = ii + 1
                If i <= UBound(y) Then z(ii) = y(i) Else z(ii) = y(UBound(y))
            Next
            z = SortArray(z)
            For i = 1 To UBound(z)
                If r <= z(i) Then
                    RaceHorseRank = i
                    Exit For
                Else
                    RaceHorseRank = UBound(z)
                End If
            Next
        End If
    Next
    
End Function


Function SortArray(Arr As Variant)
    Dim Temp, i As Long, ii As Long
    For i = LBound(Arr) To UBound(Arr) - 1
        For ii = i + 1 To UBound(Arr)
            If Arr(i) > Arr(ii) Then Temp = Arr(ii): Arr(ii) = Arr(i): Arr(i) = Temp
        Next
    Next
    SortArray = Arr
End Function

Display More

Attached is your sample file with the Functions installed.

Enter this in cell D2 and copy down.

=RaceHoresRank(C2)

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

Something not quite right Maestro? I've changed values in col C deliberately to see how the UDF responds. The Rank cells corresponding to the change are correct, but the other cells that are affected by this do not? In the attachment I changed values in 'yellow', but rows in 'red' didn't change on the UDF. A tweek is all?

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

Further to my previous post, I'm thinking that if your code works on the initial run, which is supurb, but not on amendments....who cares? I'll never be amending the values anyway. There's probably an explanation as to why that happens but my logic says that it shouldn't make any difference to the code?...Perplexed

rinconpaul · Sep 9th 2017

Re: Rank values with complex rules?

Continuing with testing Maestro. The UDF trips up when values are tied, it seems? Here is a screenshot, where I've copied and pasted 6. Cherie... alongside 3. Mr Mot.... The first two values for 6. Cherie.. were the same (3, 2.94) resulting in an error in the Rank col. In the top right I started to manually place the values below assigned Rank and the 5th Rank was not returned due to this error.

Thinking about how to handle ties: If they are tied for 4th say, in an 8 horse race, then that's how they should display - two #4's, so #5 is missing, because the odds are that ties are rare and we need to maintain the same number of rank values as horses in a race.

**KjBox** · Sep 10th 2017

Re: Rank values with complex rules?

The reason that changes to all affected ranks was not happening when a Value was changed was because I did not make the UDF a volatile function. That is easily fixed by adding a line of code to make it volatile.

Code

x = rng.Resize(, 3)
    
    With oDicHR

to

Code

x = rng.Resize(, 3)
    
    Application.Volatile
    With oDicHR

at the top of the code.

The attached workbook has the code with that change and you will see that all ranks get recalculated correctly.

With the v1.1 file I also made Horse3 and Horse5 have the same values for the first 2 entries and do not get the #N/A error. This means it must be something else structurally about "3. Mr Mot..." which is different from the other horses. Without seeing the actual data I cannot tell what that difference is.

Can you attach a file with the data that you show in the image.

rinconpaul · Sep 10th 2017

Re: Rank values with complex rules?

'Perfection', by any other name. KjBox you have surpassed all expectations above and beyond. Your skillset is nothing short of miraculous. I cannot laud you enough and by way of thanks, I present you with the Excel Cup 2017. Last issued in 2013 and only for talent, rarely seen. Thankyou so much.

**KjBox** · Sep 10th 2017

Re: Rank values with complex rules?

Thank you and you're welcome.

Rank values with complex rules?

Participate now!

Share