Can you separate a series of numbers by even and odd

[Eskonn]

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

19-11-1
19-11-2
19-11-3
19-11-4
19-12-1
19-12-2
19-12-3
19-12-4
19-13-1
19-13-2
19-13-3
19-13-4
19-14-1
19-14-2
19-14-3
19-14-4

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

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]

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?

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]

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

Material	Size	VT	Stor	Location	Qty	unit
7317515	750	VT	400	19-11-1	5	CS
5986815	750	VT	400	19-11-2	8	CS
2836015	750	VT	400	19-11-3	9	CS
6548715	750	12	400	19-11-4	8	CS
1589714	1	12	400	19-12-1	5	CS
1113410	1.75	6	400	19-12-2	3	CS
395915	750	12	400	19-12-3	9	CS
7117326	375	12	400	19-12-4	9	CS
5553015	750	VT	400	19-13-1	5	CS
6250815	750	VT	400	19-13-2	4	CS
703510	1.75	6	400	19-13-3	7	CS
673815	750	12	400	19-13-4	7	CS
262710	1.75	6	400	19-14-1	5	CS
2197115	750	12	400	19-14-2	4	CS
10050648	750	VT	400	19-14-3	7	CS
10001796	3	VT	400	19-14-4	4	CS

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

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?

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]

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

Material	Size	VT	Stor	Location	Qty	unit		Material	Size	VT	Stor	Location	Qty	unit
7317515	750	VT	400	19-11-1	5	CS		1589714	1	12	400	19-12-1	5	CS
5986815	750	VT	400	19-11-2	8	CS		1113410	1.75	6	400	19-12-2	3	CS
2836015	750	VT	400	19-11-3	9	CS		395915	750	12	400	19-12-3	9	CS
6548715	750	12	400	19-11-4	8	CS		7117326	375	12	400	19-12-4	9	CS
5553015	750	VT	400	19-13-1	5	CS		262710	1.75	6	400	19-14-1	5	CS
6250815	750	VT	400	19-13-2	4	CS		2197115	750	12	400	19-14-2	4	CS
703510	1.75	6	400	19-13-3	7	CS		10050648	750	VT	400	19-14-3	7	CS
673815	750	12	400	19-13-4	7	CS		10001796	3	VT	400	19-14-4	4	CS

<colgroup><col span="7"><col><col span="7"></colgroup><tbody>
</tbody>

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

Material	Size	VT	Stor	Location	Qty	unit
7317515	750	VT	400	19-11-1	5	CS
5986815	750	VT	400	19-11-2	8	CS
2836015	750	VT	400	19-11-3	9	CS
6548715	750	12	400	19-11-4	8	CS
1589714	1	12	400	19-12-1	5	CS
1113410	1.75	6	400	19-12-2	3	CS
395915	750	12	400	19-12-3	9	CS
7117326	375	12	400	19-12-4	9	CS
5553015	750	VT	400	19-13-1	5	CS
6250815	750	VT	400	19-13-2	4	CS
703510	1.75	6	400	19-13-3	7	CS
673815	750	12	400	19-13-4	7	CS
262710	1.75	6	400	19-14-1	5	CS
2197115	750	12	400	19-14-2	4	CS
10050648	750	VT	400	19-14-3	7	CS
10001796	3	VT	400	19-14-4	4	CS

<tbody>
</tbody>

 

[alansidman]

try this:

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]

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

Material	Size	VT	Stor	Location	Qty	unit
5986815	750	VT	400	19-11-2	8	CS
2836015	750	VT	400	19-11-3	9	CS
1113410	1.75	6	400	19-12-2	3	CS
5553015	750	VT	400	19-13-1	5	CS
6250815	750	VT	400	19-13-2	4	CS
10050648	750	VT	400	19-14-3	7	CS

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

And here is what went to sheet 2
A B C D E F G

Material	Size	VT	Stor	Location	Qty	unit
10001796	3	VT	400	19-14-4	4	CS
2197115	750	12	400	19-14-2	4	CS
262710	1.75	6	400	19-14-1	5	CS
673815	750	12	400	19-13-4	7	CS
703510	1.75	6	400	19-13-3	7	CS
7117326	375	12	400	19-12-4	9	CS
395915	750	12	400	19-12-3	9	CS
1589714	1	12	400	19-12-1	5	CS
6548715	750	12	400	19-11-4	8	CS
7317515	750	VT	400	19-11-1	5	CS

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

Again, thank you for your time

try this:

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]

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]

try this:

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:

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]

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

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:

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]

No problem, alansidman did all the hard work!

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

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

to this

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

Regards
Caleeco

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