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]

My approach would be to create a temporary sort key in a separate column, then sort the data using the temporary key. The temporary sort key would be two digits, the number of odds and the number of evens: "05", "14", "23", "32", "41", "50".

Yes?

 

[farasatbalooch]

My approach would be to create a temporary sort key in a separate column, then sort the data using the temporary key. The temporary sort key would be two digits, the number of odds and the number of evens: "05", "14", "23", "32", "41", "50".

Yes?

Sir I don't know how to create sort key because I am new to excel can you explain me more briefly

 

[Alex Blakenburg]

See if this does what you are after.
Note:
• It assumes Column B is empty, if you have anything in this column it will overwrite it
• I have left the helper column B there, uncomment the clearcontents line if you want to remove it
• I have assumed your data starts in row one and have run your code to generate the sample data
> Sorting of odds and even numbers

Sub SortOddsCnt()

    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
    
    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), "-")
        For j = 0 To UBound(arrLine)
            If arrLine(j) Mod 2 = 1 Then
                oddsCnt = oddsCnt + 1
            End If
        Next j
        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

End Sub

 

[farasatbalooch]

See if this does what you are after.
Note:
• It assumes Column B is empty, if you have anything in this column it will overwrite it
• I have left the helper column B there, uncomment the clearcontents line if you want to remove it
• I have assumed your data starts in row one and have run your code to generate the sample data
> Sorting of odds and even numbers

Sub SortOddsCnt()

    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
   
    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), "-")
        For j = 0 To UBound(arrLine)
            If arrLine(j) Mod 2 = 1 Then
                oddsCnt = oddsCnt + 1
            End If
        Next j
        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

End Sub

It's giving a a run-time error 13

Types mismatch

I don't know what it is

 

[Alex Blakenburg]

When you click on debug what line is highlighted ?
Also is your first combination on A1

 

[farasatbalooch]

When you click on debug what line is highlighted ?
Also is your first combination on A1

It's my sheet picture yes I have 5 numbers combination in column A and the same combinations in a single single cells c-d-e-f-g

 

[farasatbalooch]

It's my sheet picture yes I have 5 numbers combination in column A and the same combinations in a single single cells c-d-e-f-g

 

[farasatbalooch]

Compile VBA project option is not available

 

[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