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

 

[Eskonn]

Thank you helping with this problem.
I was able to get the A21 & J21 formulas to work.
The B21 & K21 are returning #N/A, I am trying to work through it to see what the error is.

Thanks for your time

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.

	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

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:27px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A21 {=IFERROR(SMALL(IF(ISEVEN(MID($E$2:$E$17,5,1)),ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($A$21:$A21)),"")} B21 =IF(A21="","",INDEX($A$2:$G$17,$A21,MATCH(B$20,$A$1:$G$1,0))) J21 {=IFERROR(SMALL(IF(ISODD(MID($E$2:$E$17,5,1)),ROW($E$2:$E$17)-ROW($E$2)+1),ROWS($J$3:J3)),"")} K21 =IF(J21="","",INDEX($A$2:$G$17,$J21,MATCH(A$1,$K$20:$Q$20,0))) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[alansidman]

@Caleeco
Thanks much for the correction. That's what happens when you don't test your code. I was in a hurry to run some errands.

 

[AhoyNC]

OK, see if this helps.
Sheet1 has the data.
Sheet2 is even data.
Sheet3 is odd data.

Excel Workbook
	A	B	C	D	E	F	G
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
Sheet1 - This is the data

Excel Workbook
	A	B	C	D	E	F	G	H
1	Row #	Material	Size	VT	Stor	Location	Qty	unit
2	5	1589714	1	12	400	19-12-1	5	CS
3	6	1113410	1.75	6	400	19-12-2	3	CS
4	7	395915	750	12	400	19-12-3	9	CS
5	8	7117326	375	12	400	19-12-4	9	CS
6	13	262710	1.75	6	400	19-14-1	5	CS
7	14	2197115	750	12	400	19-14-2	4	CS
8	15	10050648	750	VT	400	19-14-3	7	CS
9	16	10001796	3	VT	400	19-14-4	4	CS
Sheet2 - Even data

Excel Workbook
	A	B	C	D	E	F	G	H
1	Row #	Material	Size	VT	Stor	Location	Qty	unit
2	1	7317515	750	VT	400	19-11-1	5	CS
3	2	5986815	750	VT	400	19-11-2	8	CS
4	3	2836015	750	VT	400	19-11-3	9	CS
5	4	6548715	750	12	400	19-11-4	8	CS
6	9	5553015	750	VT	400	19-13-1	5	CS
7	10	6250815	750	VT	400	19-13-2	4	CS
8	11	703510	1.75	6	400	19-13-3	7	CS
9	12	673815	750	12	400	19-13-4	7	CS
Sheet3 - Odd data