Can you separate a series of numbers by even and odd

Eskonn · May 9, 2018

I have a large series of numbers that I need separated by even and odd numbers.
Here is what the numbers look like. I have made the odd numbers red on the list below and the even numbers are black

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]19-11-1[/TD]
[/TR]
[TR]
[TD]19-11-2[/TD]
[/TR]
[TR]
[TD]19-11-3[/TD]
[/TR]
[TR]
[TD]19-11-4[/TD]
[/TR]
[TR]
[TD]19-12-1[/TD]
[/TR]
[TR]
[TD]19-12-2[/TD]
[/TR]
[TR]
[TD]19-12-3[/TD]
[/TR]
[TR]
[TD]19-12-4[/TD]
[/TR]
[TR]
[TD]19-13-1[/TD]
[/TR]
[TR]
[TD]19-13-2[/TD]
[/TR]
[TR]
[TD]19-13-3[/TD]
[/TR]
[TR]
[TD]19-13-4[/TD]
[/TR]
[TR]
[TD]19-14-1[/TD]
[/TR]
[TR]
[TD]19-14-2[/TD]
[/TR]
[TR]
[TD]19-14-3[/TD]
[/TR]
[TR]
[TD]19-14-4[/TD]
[/TR]
</tbody>[/TABLE]

The digit that determines if it is even or odd is the fourth number 19-14-1
When separating these I would like to separate the whole row.

Any suggestions would be great.
Thanks

alansidman · May 9, 2018

Assume your data is in column A beginning in Row 1. The following VBA will highlight the Odd numbers in Green Background. I was not sure what you meant by separate. Please clarify. Separate to where? Delete?

Code:

Option Explicit


Sub EvenOdd()
    Dim lr As Long, i As Long, x As Variant
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        x = Mid(Range("A" & i), 5, 1)
        If x Mod 2 Then
            Range("A" & i).Interior.ColorIndex = 4
        End If
    Next i
End Sub

Eskonn · May 9, 2018

When I import my data the list all comes together. I don't want to delete the odd numbers but would like them in different columns so I can easily sum columns and compare volume of product sold between the even and odd numbers. Hope that made sense.
Below is a better look at how the data comes into excel.
I am trying to compare column F in the example below

A B C D E F G
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Material[/TD]
[TD="class: xl65, width: 64"]Size[/TD]
[TD="class: xl65, width: 64"]VT[/TD]
[TD="class: xl65, width: 64"]Stor[/TD]
[TD="class: xl65, width: 64"]Location[/TD]
[TD="class: xl65, width: 64"]Qty[/TD]
[TD="class: xl65, width: 64"]unit[/TD]
[/TR]
[TR]
[TD="class: xl65"]7317515[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]5986815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-2[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]2836015[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]6548715[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]1589714[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]1113410[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]395915[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]7117326[/TD]
[TD="class: xl65"]375[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-4[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]5553015[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]6250815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]703510[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-3[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]673815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-4[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]262710[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]2197115[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]10050648[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-3[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]10001796[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
</tbody>[/TABLE]

alansidman said:
Assume your data is in column A beginning in Row 1. The following VBA will highlight the Odd numbers in Green Background. I was not sure what you meant by separate. Please clarify. Separate to where? Delete?

Code:

Option Explicit Sub EvenOdd() Dim lr As Long, i As Long, x As Variant lr = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To lr x = Mid(Range("A" & i), 5, 1) If x Mod 2 Then Range("A" & i).Interior.ColorIndex = 4 End If Next i End Sub

Eskonn · May 9, 2018

Below is an example of how I would like the data to look. Maybe this helps explain better what I am looking for. in the below data column H is blank

[TABLE="width: 928"]
<colgroup><col span="7"><col><col span="7"></colgroup><tbody>[TR]
[TD]Material[/TD]
[TD]Size[/TD]
[TD]VT[/TD]
[TD]Stor[/TD]
[TD]Location[/TD]
[TD]Qty[/TD]
[TD]unit[/TD]
[TD][/TD]
[TD]Material[/TD]
[TD]Size[/TD]
[TD]VT[/TD]
[TD]Stor[/TD]
[TD]Location[/TD]
[TD]Qty[/TD]
[TD]unit[/TD]
[/TR]
[TR]
[TD]7317515[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-11-1[/TD]
[TD]5[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]1589714[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-12-1[/TD]
[TD]5[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]5986815[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-11-2[/TD]
[TD]8[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]1113410[/TD]
[TD]1.75[/TD]
[TD]6[/TD]
[TD]400[/TD]
[TD]19-12-2[/TD]
[TD]3[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]2836015[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-11-3[/TD]
[TD]9[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]395915[/TD]
[TD]750[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-12-3[/TD]
[TD]9[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]6548715[/TD]
[TD]750[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-11-4[/TD]
[TD]8[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]7117326[/TD]
[TD]375[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-12-4[/TD]
[TD]9[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]5553015[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-13-1[/TD]
[TD]5[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]262710[/TD]
[TD]1.75[/TD]
[TD]6[/TD]
[TD]400[/TD]
[TD]19-14-1[/TD]
[TD]5[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]6250815[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-13-2[/TD]
[TD]4[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]2197115[/TD]
[TD]750[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-14-2[/TD]
[TD]4[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]703510[/TD]
[TD]1.75[/TD]
[TD]6[/TD]
[TD]400[/TD]
[TD]19-13-3[/TD]
[TD]7[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]10050648[/TD]
[TD]750[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-14-3[/TD]
[TD]7[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD]673815[/TD]
[TD]750[/TD]
[TD]12[/TD]
[TD]400[/TD]
[TD]19-13-4[/TD]
[TD]7[/TD]
[TD]CS[/TD]
[TD][/TD]
[TD]10001796[/TD]
[TD]3[/TD]
[TD]VT[/TD]
[TD]400[/TD]
[TD]19-14-4[/TD]
[TD]4[/TD]
[TD]CS[/TD]
[/TR]
</tbody>[/TABLE]

Eskonn said:
When I import my data the list all comes together. I don't want to delete the odd numbers but would like them in different columns so I can easily sum columns and compare volume of product sold between the even and odd numbers. Hope that made sense.
Below is a better look at how the data comes into excel.
I am trying to compare column F in the example below

A B C D E F G
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Material[/TD]
[TD="class: xl65, width: 64"]Size[/TD]
[TD="class: xl65, width: 64"]VT[/TD]
[TD="class: xl65, width: 64"]Stor[/TD]
[TD="class: xl65, width: 64"]Location[/TD]
[TD="class: xl65, width: 64"]Qty[/TD]
[TD="class: xl65, width: 64"]unit[/TD]
[/TR]
[TR]
[TD="class: xl65"]7317515[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]5986815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-2[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]2836015[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]6548715[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-11-4[/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]1589714[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]1113410[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-2[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]395915[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]7117326[/TD]
[TD="class: xl65"]375[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-12-4[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]5553015[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]6250815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]703510[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-3[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]673815[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-13-4[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]262710[/TD]
[TD="class: xl65"]1.75[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-1[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]2197115[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]12[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-2[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]10050648[/TD]
[TD="class: xl65"]750[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-3[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
[TR]
[TD="class: xl65"]10001796[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]VT[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl65"]19-14-4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]CS[/TD]
[/TR]
</tbody>[/TABLE]

alansidman · May 9, 2018

try this:

Code:

Option Explicit


Sub EvenOdd()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:G1").Copy s2.Range("A1")
    Dim lr As Long, i As Long, x As Variant, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        x = Mid(s1.Range("A" & i), 5, 1)
        If x Mod 2 Then
            s1.Range("A" & i).EntireRow.Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
            s1.Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

Eskonn · May 9, 2018

First, Thank you for your quick reply and trying to help me solve this.
When I ran that it did split the data between 2 sheets which would be excellent. However I am not sure what it used to split the data. There are still even and odds on both sheets. I will post the results below.
Just to make sure I explained correctly in column E I want to split the odd numbers to a new sheet. This is how the number looks 19-11-1. The numbers will always be in this format and the same number of characters. The red number is the number that determines if it is even or odd.

Here is how the list was split between the sheets.
Sheet 1
A B C D E F G
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Material[/TD]
[TD="class: xl63, width: 64"]Size[/TD]
[TD="class: xl63, width: 64"]VT[/TD]
[TD="class: xl63, width: 64"]Stor[/TD]
[TD="class: xl63, width: 64"]Location[/TD]
[TD="class: xl63, width: 64"]Qty[/TD]
[TD="class: xl63, width: 64"]unit[/TD]
[/TR]
[TR]
[TD="class: xl63"]5986815[/TD]
[TD="class: xl63"]750[/TD]
[TD="class: xl63"]VT[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-11-2[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
[TR]
[TD="class: xl63"]2836015[/TD]
[TD="class: xl63"]750[/TD]
[TD="class: xl63"]VT[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-11-3[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
[TR]
[TD="class: xl63"]1113410[/TD]
[TD="class: xl63"]1.75[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-12-2[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
[TR]
[TD="class: xl63"]5553015[/TD]
[TD="class: xl63"]750[/TD]
[TD="class: xl63"]VT[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-13-1[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
[TR]
[TD="class: xl63"]6250815[/TD]
[TD="class: xl63"]750[/TD]
[TD="class: xl63"]VT[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-13-2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
[TR]
[TD="class: xl63"]10050648[/TD]
[TD="class: xl63"]750[/TD]
[TD="class: xl63"]VT[/TD]
[TD="class: xl63"]400[/TD]
[TD="class: xl63"]19-14-3[/TD]
[TD="class: xl63"]7[/TD]
[TD="class: xl63"]CS[/TD]
[/TR]
</tbody>[/TABLE]

And here is what went to sheet 2
A B C D E F G
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Material[/TD]
[TD="class: xl65, width: 64"]Size[/TD]
[TD="class: xl65, width: 64"]VT[/TD]
[TD="class: xl65, width: 64"]Stor[/TD]
[TD="class: xl65, width: 64"]Location[/TD]
[TD="class: xl65, width: 64"]Qty[/TD]
[TD="class: xl65, width: 64"]unit[/TD]
[/TR]
[TR]
[TD="align: right"]10001796[/TD]
[TD="align: right"]3[/TD]
[TD]VT[/TD]
[TD="align: right"]400[/TD]
[TD]19-14-4[/TD]
[TD="align: right"]4[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]2197115[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-14-2[/TD]
[TD="align: right"]4[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]262710[/TD]
[TD="align: right"]1.75[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]400[/TD]
[TD]19-14-1[/TD]
[TD="align: right"]5[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]673815[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-13-4[/TD]
[TD="align: right"]7[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]703510[/TD]
[TD="align: right"]1.75[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]400[/TD]
[TD]19-13-3[/TD]
[TD="align: right"]7[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]7117326[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-12-4[/TD]
[TD="align: right"]9[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]395915[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-12-3[/TD]
[TD="align: right"]9[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]1589714[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-12-1[/TD]
[TD="align: right"]5[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]6548715[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]400[/TD]
[TD]19-11-4[/TD]
[TD="align: right"]8[/TD]
[TD]CS[/TD]
[/TR]
[TR]
[TD="align: right"]7317515[/TD]
[TD="align: right"]750[/TD]
[TD]VT[/TD]
[TD="align: right"]400[/TD]
[TD]19-11-1[/TD]
[TD="align: right"]5[/TD]
[TD]CS[/TD]
[/TR]
</tbody>[/TABLE]

Again, thank you for your time

alansidman said:

try this:

Code:

Option Explicit


Sub EvenOdd()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:G1").Copy s2.Range("A1")
    Dim lr As Long, i As Long, x As Variant, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        x = Mid(s1.Range("A" & i), 5, 1)
        If x Mod 2 Then
            s1.Range("A" & i).EntireRow.Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
            s1.Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

AhoyNC · May 9, 2018

Here is a formula way.
The formula for the Row # (cells A21 and J21) are array formula and must be entered with CTRL-SHIFT-ENTER then drag down as needed.
Formulas in B21 and J21 just copy down and across.Change ranges to match your data. They can be put on separate worksheets.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Material

Size

VT

Stor

Location

Qty

unit

2

7317515

750

VT

400

19-11-1

5

CS

3

5986815

750

VT

400

19-11-2

8

CS

4

2836015

750

VT

400

19-11-3

9

CS

5

6548715

750

12

400

19-11-4

8

CS

6

1589714

1

12

400

19-12-1

5

CS

7

1113410

1.75

6

400

19-12-2

3

CS

8

395915

750

12

400

19-12-3

9

CS

9

7117326

375

12

400

19-12-4

9

CS

10

5553015

750

VT

400

19-13-1

5

CS

11

6250815

750

VT

400

19-13-2

4

CS

12

703510

1.75

6

400

19-13-3

7

CS

13

673815

750

12

400

19-13-4

7

CS

14

262710

1.75

6

400

19-14-1

5

CS

15

2197115

750

12

400

19-14-2

4

CS

16

10050648

750

VT

400

19-14-3

7

CS

17

10001796

3

VT

400

19-14-4

4

CS

18

19

Even #'s

ODD #'s

20

Row #

Material

Size

VT

Stor

Location

Qty

unit

Row #

Material

Size

VT

Stor

Location

Qty

unit

21

5

1589714

1

12

400

19-12-1

5

CS

1

7317515

750

VT

400

19-11-1

5

CS

22

6

1113410

1.75

6

400

19-12-2

3

CS

2

5986815

750

VT

400

19-11-2

8

CS

23

7

395915

750

12

400

19-12-3

9

CS

3

2836015

750

VT

400

19-11-3

9

CS

24

8

7117326

375

12

400

19-12-4

9

CS

4

6548715

750

12

400

19-11-4

8

CS

25

13

262710

1.75

6

400

19-14-1

5

CS

9

5553015

750

VT

400

19-13-1

5

CS

26

14

2197115

750

12

400

19-14-2

4

CS

10

6250815

750

VT

400

19-13-2

4

CS

27

15

10050648

750

VT

400

19-14-3

7

CS

11

703510

1.75

6

400

19-13-3

7

CS

28

16

10001796

3

VT

400

19-14-4

4

CS

12

673815

750

12

400

19-13-4

7

CS

29

Sheet

Caleeco · May 9, 2018

alansidman said:

try this:

Code:

Option Explicit


Sub EvenOdd()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:G1").Copy s2.Range("A1")
    Dim lr As Long, i As Long, x As Variant, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        x = Mid(s1.Range("A" & i), 5, 1)
        If x Mod 2 Then
            s1.Range("A" & i).EntireRow.Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
            s1.Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

Not to steal your thunder, but there is a small error in your code. You find the MID for data in column A instead of column E.

This should work:

Code:

Sub EvenOdd()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:G1").Copy s2.Range("A1")
    Dim lr As Long, i As Long, x As Variant, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        x = Mid(s1.Range("E" & i), 5, 1) 'corrected this line
        If x Mod 2 Then
            s1.Range("A" & i).EntireRow.Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
            s1.Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

Hope that helps
Caleeco

Eskonn · May 9, 2018

Yes, that worked!
Thanks for the help.

I have an additional question.
If the data I import has more columns and the location (column E) switches to Column L, What would I need to change in your code to split my data correctly?

Hope that makes sense

Caleeco said:

Not to steal your thunder, but there is a small error in your code. You find the MID for data in column A instead of column E.

This should work:

Code:

Sub EvenOdd()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    s1.Range("A1:G1").Copy s2.Range("A1")
    Dim lr As Long, i As Long, x As Variant, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        x = Mid(s1.Range("E" & i), 5, 1) 'corrected this line
        If x Mod 2 Then
            s1.Range("A" & i).EntireRow.Copy
            s2.Range("A" & lr2 + 1).PasteSpecial xlPasteValues
            s1.Range("A" & i).EntireRow.Delete
        End If
    Next i
End Sub

Hope that helps
Caleeco

Caleeco · May 9, 2018

No problem, alansidman did all the hard work!

You would just need to change one letter in the line below from this:

Code:

[COLOR=#333333][I]x = Mid(s1.Range("E" & i), 5, 1) 'corrected this line[/I][/COLOR]

to this

Code:

[COLOR=#333333][I]x = Mid(s1.Range("L" & i), 5, 1) 'corrected this line[/I][/COLOR]

Regards
Caleeco

Eskonn said:
Yes, that worked!
Thanks for the help.

I have an additional question.
If the data I import has more columns and the location (column E) switches to Column L, What would I need to change in your code to split my data correctly?

Hope that makes sense

Can you separate a series of numbers by even and odd

Eskonn

New Member

Excel Facts

alansidman

Well-known Member

Eskonn

New Member

Eskonn

New Member

alansidman

Well-known Member

Eskonn

New Member

AhoyNC

Well-known Member

Caleeco

Well-known Member

Eskonn

New Member

Caleeco

Well-known Member

Similar threads

Forum statistics

Share this page

Can you separate a series of numbers by even and odd

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock