Sorting of odds and even numbers

[farasatbalooch]

I want to sort odds and even numbers in a list of 5 numbers combination what is formula and how to apply it.

My list Example
5-16-9-21-44
34-22-28-41-39
6-12-18-29-45
11-15-34-32-43
28-27-44-49-47

How to sort odds and even number in this type of list.

 

[Rick Rothstein]

Wait a minute. You have about 2 million cells with number combinations like your original post showed? Obviously, they are not in a single column... what is the cell range for your data then? One thing I might worry about is the size workbook after you color all that data... coloring individual text strings is "expensive" memory-wise and you are wanting to color around 10 million numbers (15 to 20 million individual characters)... I am really worried running the macro may break your workbook. Are you sure you want to pursue this?

 

[farasatbalooch]

Wait a minute. You have about 2 million cells with number combinations like your original post showed? Obviously, they are not in a single column... what is the cell range for your data then? One thing I might worry about is the size workbook after you color all that data... coloring individual text strings is "expensive" memory-wise and you are wanting to color around 10 million numbers (15 to 20 million individual characters)... I am really worried running the macro may break your workbook. Are you sure you want to pursue this?

Yes sir if you make me the code I will run it and see the result because it is impossible for me to figure out the odds and even numbers manually

 

[Rick Rothstein]

What if I gave you code that put the odd numbers in one cell and the even numbers in another (on the same row as the original data cell), would that aid you in doing whatever it is you are doing with your data? This would not balloon your workbook's size or risk crashing Excel. No matter what your answer is to this question, I still need to know what range your existing data is in.

 

[farasatbalooch]

What if I gave you code that put the odd numbers in one cell and the even numbers in another (on the same row as the original data cell), would that aid you in doing whatever it is you are doing with your data? This would not balloon your workbook's size or risk crashing Excel. No matter what your answer is to this question, I still need to know what range your existing data is in.

my cell range is from 1 to 49 5 numbers combination from 1 to 49 and i have attached the vba code for making my 5 numbers combination

VBA CODE

Sub ListThemAll()
TC = 1
TR = 1
Ctr = 1
MaxRows = Rows.Count
EndCell = 1906884
Application.ScreenUpdating = False
For a = 1 To 45
For b = (a + 1) To 46
For c = (b + 1) To 47
For d = (c + 1) To 48
For e = (d + 1) To 49
Application.StatusBar = Ctr & " on way to " & EndCell
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
Cells(TR - 20, TC).Select
Application.ScreenUpdating = True
ThisWorkbook.Save
Application.ScreenUpdating = False
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e
Next d
Next c
Next b
Next a
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

 

[farasatbalooch]

What if I gave you code that put the odd numbers in one cell and the even numbers in another (on the same row as the original data cell), would that aid you in doing whatever it is you are doing with your data? This would not balloon your workbook's size or risk crashing Excel. No matter what your answer is to this question, I still need to know what range your existing data is in.

my cell range is from 1 to 49 5 numbers combination from 1 to 49 and i have attached the vba code for making my 5 numbers combination

VBA CODE

Sub ListThemAll()
TC = 1
TR = 1
Ctr = 1
MaxRows = Rows.Count
EndCell = 1906884
Application.ScreenUpdating = False
For a = 1 To 45
For b = (a + 1) To 46
For c = (b + 1) To 47
For d = (c + 1) To 48
For e = (d + 1) To 49
Application.StatusBar = Ctr & " on way to " & EndCell
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
Cells(TR - 20, TC).Select
Application.ScreenUpdating = True
ThisWorkbook.Save
Application.ScreenUpdating = False
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e
Next d
Next c
Next b
Next a
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Quote Reply

 

[lrobbo314]

I would like to say that @Rick Rothstein is correct. This should NOT be used on your workbook. It will surely fail as there will be way to many font styles.

I don't know what analysis you could possible do with ~2 million combinations... But, I would probably suggest just using 'text to columns' to separate the combination values and sort them from there.

But having said that, I couldn't help myself but to throw out a solution that works on the sample data.

I thought it would be fun to come up with a solution that used ifless programming.

Here is what I came up with.

Sub HEO()
Dim RNG As Range:     Set RNG = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Dim CEL As Range
Dim SP() As String
Dim POS As Integer, TL As Integer
Dim CVAL As Long:   CVAL = 39424 'Bright Colors: 65280, Dark Colors: 39424

For Each CEL In RNG
    SP = Split(CEL.Value, "-")
    POS = 1
    For i = 0 To UBound(SP)
        TL = Len(SP(i))
        CEL.Characters(POS, TL).Font.Color = CVAL + ((CVAL * 255) * Abs(Int(SP(i)) Mod 2 = 1))
        POS = POS + TL + 1
    Next i
Next CEL

End Sub

 

[farasatbalooch]

Yes I want to highlight like this is it possible in ms excel 2013?

Yes it possible you can do it with flash file option