Trying To Break ties

[Chesley]

I have a column with a number of integers in column A, which has a number of ties in it. I need to be able to break the ties by using values in B, and if there is still ties, then got column c, and again, if there is ties go to column D,etc ( up to 7 columns with values to be used to break the Ties). I'm trying to do this in vba, however being new to vba , I haven't been able to get this to work, I find it gets to convoluted when I get to checking the second column of values to try and break the ties, I have all values stored in arrays. As you can see below the first "77" tie is broken by the next column "36" but the next two 77 ties are not broken until the last possible check.

77 36 24 12 41 27 13 5
77 37 23 11 40 27 13 4
77 37 23 11 40 27 13 5

I've been trying to resolve this for a couple of weeks, sad to say I haven't, but the good news is I'm learning a lot about Excel VBA. If someone can point me in the right direction it would be appreciated.

Thanks

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

In VBA terms, whet do you want to happen when you have located a tie (or not)? Do you want a flag to be set in an array element or something?

The trick to these problems is to steer away from a detailed solution. Down that path lies madness. Basically, all you need to is:

1. Combine all the numbers together into a string separated by a character to stop the columns getting mixed up. E.G 77-36-24-12-41-27-13-5
2. Sort that string into order.
3. See if a string matches either of its neighbours.

An alternative to steps 2 and 3 would be to count up the number of occurrences of each string and report the results. Which is the better option depends on how you need the answers.

 

[shg]

I think that won't sort properly if any of the numbers are < 10.

If a UDF is an alternative,

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	​	​	​	​	​	​	​	​	​	sort​	​
2​	77​	36​	24​	12​	41​	27​	13​	5​		7736241241271305	J2: =Cat(INDEX(TEXT(A2:H2, "00"), 0), "")
3​	77​	37​	23​	11​	40​	27​	13​	4​		7737231140271304
4​	77​	37​	23​	11​	40​	27​	13​	5​		7737231140271305

If any of the numbers can exceed 99, change "00" to "000"

Function Cat(vInp As Variant, _
             Optional sSep As String = ",", _
             Optional bCatEmpty As Boolean = False) As String
  ' Catenates the elements of vInp separated by sSep
  ' Empty values and null strings are ignored unless bCatEmpty is True

  Dim vItem         As Variant
  Dim sItem         As String

  If bCatEmpty Then
    For Each vItem In vInp
      Cat = Cat & CStr(vItem) & sSep
    Next vItem

  Else
    For Each vItem In vInp
      sItem = CStr(vItem)
      If Len(sItem) Then Cat = Cat & sItem & sSep
    Next vItem
  End If

  If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

 

[RickXL]

I think that won't sort properly if any of the numbers are < 10.

I convinced myself that as "-" was less than "1" it would work OK for one and two digit numbers.

It still sorts 99-10 in front of 99-9 though.

Sorry about that and thanks to shg for catching it.
Let's hope that the brain works properly for the rest of the day.

Regards,

 

[shg]

Sorry about that and thanks to shg for catching it.

Yup -- almost everything I know about Excel is a result of learning from my own mistakes. Way more errors than careful forethought.

 

[Chesley]

Thanks Rick, I've looked info on comparing strings, been playing with, think it may have possibilities. I don't really understand what you mean in your last statement however. I've also looked at "shgs" response, I like the idea, but am afraid I don't really understand it. One thing I neglected to state all the values in the second to 8th column are not actually in columns but stored in arrays, ie. the Nextnums array contains the 36,37,37 and the next array contains 24,23,23 etc, these values are derived from summing values in a number of cells in the table. For now I'm trying to figure out how to loop through the strings with the strcomp function. Loops inside Loops, I'm getting more confused.

Thank you both for your assistance.

 

[RickXL]

Yes, I understand about the arrays but I still don't know how you want your answer to be presented. Do you want to add a column to your existing array that says Tied/NotTied in it? Or something else?

If you tell me what you want I can tell you how to do it.

 

[Chesley]

OK Rick, what I would like in the end is to right in a cell to the right of the Original "77" column something like "1st", keep in mind I'm only showing ties here but the actual table will have unique values and possibly other ties which will have to be sorted out also, ie, it could be , 75,76,77,77,77,79,80,81,83,84,84,84,86,87,89, etc.
I would like to end with
75 1st
76 2nd
77 3rd
77 4th
77 5th
79 6th etc...

Does this help?

Thanks again.

 

[mikerickson]

Perhaps if your array is module wide you could use this test to see if one row were LessThan another.

Function LT(aRow as Long, bRow As Long) As Booolean
    Dim i as Long

    For i = LBound(myArray, 2) to UBound(myArray, 2)
        If myArray(aRow, i) <> myArray(bRow, i) Then
            LT = (myArray(aRow, i) < myArray(bRow, i))
            Exit For
        End If
    Next i
End Function

' companion functions

Function GT(aRow as Long, bRow as Long) As Boolean
    GT = LT(bRow, aRow)
End Function

Function LE(aRow as Long, bRow as Long) As Boolean
    LE = Not(GT(aRow, bRow))
End Function

Function GE(aRow as Long, bRow as Long) As Boolean
    GE = Not(LT(aRow, bRow))
End Function

Function EQ(aRow as Long, bRow as Long) As Boolean
    EQ = LE(aRow, bRow) And GE(aRow, bRow)
End Function

Function NE(aRow as Long, bRow as Long) As Boolean
    NE = LT(aRow, bRow) Or GT(aRow, bRow)
End Function

 

[mikerickson]

The sorting technique would be

Dim myArray() As Variant

Sub SortARange()
    Dim arrRowNumbers() As Long
    Dim SortedArray() As Variant
    Dim destinationRange As Range
    Dim i As Long, j As Long, temp As Long
    
    myArray = Range("A1").CurrentRegion: Rem adjust
    Set destinationRange = Range("Q1"): Rem adjust

    ReDim arrRowNumbers(1 To UBound(myArray, 1))
    For i = 1 To UBound(arrRowNumbers): arrRowNumbers(i) = i: Next i
    
    For i = 1 To UBound(arrRowNumbers) - 1
        For j = i + 1 To UBound(arrRowNumbers)
            If LT(arrRowNumbers(j), arrRowNumbers(i)) Then
                temp = arrRowNumbers(i)
                arrRowNumbers(i) = arrRowNumbers(j)
                arrRowNumbers(j) = temp
            End If
        Next j
    Next i
    
    SortedArray = myArray ' in lieu of redim
    For i = 1 To UBound(SortedArray, 1)
        For j = 1 To UBound(SortedArray, 2)
            SortedArray(i, j) = myArray(arrRowNumbers(i), j)
        Next j
    Next i
    
    destinationRange.Resize(UBound(SortedArray, 1), UBound(SortedArray, 2)).Value = SortedArray
End Sub

Function LT(aRow As Long, bRow As Long) As Boolean
    Dim i As Long

    For i = LBound(myArray, 2) To UBound(myArray, 2)
        If myArray(aRow, i) <> myArray(bRow, i) Then
            LT = (myArray(aRow, i) < myArray(bRow, i))
            Exit For
        End If
    Next i
End Function