sorting by coloured cell

[farasatbalooch]

hello!
I want to sort odds and even numbers combination in ms excel 2013 i have a list of 2 million combination my combination are mixed and i want to sort them for example
(3 odd 2 even numbers all combination together , 4 odd 1 even numbers all combination together , 2 odd 3 even numbers all combination together
1 odd 4 even numbers all combination together) the list i have create is for lotto i have 5 numbers combination from numbers 1 to 49 numbers for example
i want to sort like this from the mix combination list i have any vba code or formulas anybody knows kindly tell me it would be a great help thanks.

1-3-6-40-41 1-3-6-40-43 1-3-6-40-42 1-3-6-40-44 3-9-15-17-40 3-9-15-17-42 3-6-24-34-44 3-6-24-34-46

 

[Ruddles]

Without using VBA

If your combinations are in column A:-
Put this in B2: =TEXTSPLIT(A2,"-") – this will split your combination across cols B:F
Put this in G2 and copy across to H2:K2: =IF(MOD(B2,2)=1,"O","E")
Put this in L2: =COUNTIF(G2:K2,"O")&COUNTIF(G2:K2,"E")
L2 now contains a sort key detailing how many odd numbers and how many even numbers are in A2. Copy cols B:L down to the end of your data and you can now sort your combinations using this as the key.

Set L2 to =COUNTIF(G2:K2,"E")&COUNTIF(G2:K2,"O") if you want to show the evens before the odds

For separate odd and even keys, set L2 to =COUNTIF(G2:K2,"O") and M2 to =COUNTIF(G2:K2,"E")

Any good?

 

[Fluff]

Textsplit does exist in 2013, only 365.

 

[Sanjeev1976]

Hi,
You can use this formula in column B
=SUMPRODUCT(--(MOD(TEXTSPLIT(A1,"-"),2)=1)) and then drag it to all the 2 million combinations and then sort the data

Sorry this will not work for your version of excel

 

[Alex Blakenburg]

It's giving a a run-time error 13

Types mismatch

I don't know what it is

You didn't mention which line was highlighted when you clicked on debug.
My guess is that you have some data in your data set that is not numeric and that Mod 2 is erroring out.

The below will pop up a message box if that happens for the first error.
The row no it gives is the original row no since after the sort it will sort to the bottom.

Sub SortOddsCnt_v02()

    Dim ws As Worksheet
    Dim rngData As Range
    Dim arrData As Variant, arrLine As Variant
    Dim rowLast As Long, oddsCnt As Long
    Dim i As Long, j As Long
   
    Dim isOdd As Variant
    Dim errMsg As String
   
    Set ws = ActiveSheet
    rowLast = ws.Range("A" & Rows.Count).End(xlUp).Row
    Set rngData = ws.Range("A1:A" & rowLast).Resize(, 2)    ' Assume next column is empty
    arrData = rngData.Value
   
    For i = 1 To UBound(arrData)
        oddsCnt = 0
        arrLine = Split(arrData(i, 1), "-")
        On Error Resume Next
        For j = 0 To UBound(arrLine)
            isOdd = arrLine(j) Mod 2 = 1

            If Err <> 0 And errMsg = "" Then
                ' Capture 1st row with an issue
                errMsg = "First Error Row No: " & i _
                            & Chr(10) & "Value: " & arrData(i, 1) _
                            & Chr(10) & "After sort will be at the bottom"
                oddsCnt = 999       ' Set Dummy value to sort to the bottom
                Exit For
            ElseIf isOdd Then
                oddsCnt = oddsCnt + 1
            End If
        Next j
        On Error GoTo 0
        arrData(i, 2) = "Odds No: " & oddsCnt
   
    Next i
    rngData.Columns(2) = Application.Index(arrData, 0, 2)
   
    ws.Sort.SortFields.Clear
    rngData.Sort Key1:=ws.Range("B1"), _
                     Order1:=xlAscending, _
                     Header:=xlNo
                    
    ' rngData.Columns(2).ClearContents      ' Uncomment to clear helper column data
   
    MsgBox errMsg

End Sub

 

[Sanjeev1976]

Sorry this will not work for your version of excel

You can use this formula in column B
=SUMPRODUCT(--(MOD(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,"-","</s><s>")& "</s></t>","//s")),2)=1)) and then drag it to all the 2 million combinations and then sort the data