Split 6 number in to 14 cells as per order finds

[motilulla]

Hello,</SPAN>

I want to split columns C:H numbers, in to columns K:X, how I need that the C:H 6 numbers to be split in 14 cells in to K:X columns. Here I will try to explain, look up all C:H numbers in to in the cells K5:X5 (where I have got 14 numbers as follow.... 0,0,1,1,1,0,4,0,2,0,2,2,1,0) Now for the example...</SPAN></SPAN>

Split all Column C:H number as find in the order 1st into header K5:X5 </SPAN></SPAN>

C6=0 as K5 is 0 (place 0 into K6) </SPAN></SPAN>
D6=0 as L5 is 0 (place 0 into L6)</SPAN></SPAN>
E6=1 as M5 is 1 (place 1 into M6)</SPAN></SPAN>
F6=1 as N5 is 1 (place 1 into N6)</SPAN></SPAN>
G6=0 as P5 is 0 (place 0 into P6)</SPAN></SPAN>
H6=4 as Q5 is 4 (place 0 into Q6)</SPAN></SPAN>

The same split method will be follow for the rest of rows Columns C:H</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4			n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
5			n1	n2	n3	n4	n5	n6			0	0	1	1	1	0	4	0	2	0	2	2	1	0
6			0	0	1	1	0	4			0	0	1	1		0	4
7			0	0	1	1	4	0			0	0	1	1			4	0
8			0	0	1	1	2	2			0	0	1	1							2	2
9			0	0	1	0	4	1			0	0			1	0	4						1
10			0	0	1	4	0	1			0	0	1				4	0					1
11			0	0	1	4	1	0			0	0	1				4						1	0
12			0	0	1	2	2	1			0	0	1						2		2		1
13			0	0	0	4	0	2			0	0				0	4	0	2
14			0	0	0	4	2	0			0	0				0	4		2	0
15			0	0	0	2	2	2			0	0				0			2		2	2
16			0	0	4	0	2	0			0	0					4	0	2	0
17			0	0	4	0	0	2			0	0					4	0		0	2
18			0	0	4	2	0	0			0	0					4		2	0				0
19			0	0	2	0	2	2			0	0							2	0	2	2
20			0	0	2	2	2	0			0	0							2		2	2		0
21			0	1	1	1	2	1			0		1	1	1				2				1
22			0	1	1	0	4	0			0		1	1		0	4	0
23			0	1	1	0	2	2			0		1	1		0					2	2
24			0	1	1	4	0	0			0		1	1			4	0		0
25			0	1	1	2	0	2			0		1	1					2	0	2
26			0	1	1	2	2	0			0		1	1					2		2			0
27			0	1	0	4	0	1			0		1			0	4	0					1
28			0	1	0	4	1	0			0		1			0	4						1	0
29			0	1	0	2	2	1			0		1			0			2			2	1
30			0	1	4	0	0	1			0		1				4	0		0			1
31			0	1	4	0	1	0			0		1				4	0					1	0
32			0	1	2	0	2	1			0		1						2	0	2		1
33			0	1	2	2	1	0			0		1						2		2		1	0
34			0	4	0	2	0	0			0						4	0	2	0				0
35			0	4	0	0	2	0			0						4	0		0	2			0
36			0	2	0	2	2	0			0								2	0	2	2		0
37			1	1	1	0	2	1					1	1	1	0						2	1
38			1	1	1	2	0	1					1	1	1				2	0			1
39			1	1	1	2	1	0					1	1	1				2				1	0
40			1	1	0	4	0	0					1	1		0	4	0		0
41			1	1	0	0	2	2					1	1		0		0	2		2
42			1	1	0	2	0	2					1	1		0			2	0	2
43			1	1	0	2	2	0					1	1		0			2		2			0
44			1	1	4	0	0	0					1	1			4	0		0				0
45			1	1	2	0	2	0					1	1					2	0	2			0
46			1	0	4	0	0	1					1			0	4	0		0			1
47			1	0	4	0	1	0					1			0	4	0					1	0
48			1	0	0	2	2	1					1			0		0	2		2		1
49			1	0	2	0	2	1					1			0			2	0		2	1
50			1	0	2	2	1	0					1			0					2	2	1	0
51			1	4	0	0	1	0					1			0	4	0					1	0
52			1	2	0	2	1	0					1						2	0	2		1	0
Sheet2

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN></SPAN>

 

[DanteAmor]

Is it possible that you have errors in your example? p.e on line 8


G8 = 2, the first 2 into header K5:X5 is S5, Then, the split must be:

	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
5		0	0	1	1	1	0	4	0	2	0	2	2	1	0
8		0	0	1	1					2		2

<tbody>
</tbody>

If the above is correct, run this macro:

Sub Split_Number()
'
    Application.ScreenUpdating = False
    u = Range("C" & Rows.Count).End(xlUp).Row
    Range("K6:X" & u).ClearContents
    For i = 6 To u
        cini = Columns("J").Column
        cfin = Columns("X").Column
        For j = Columns("C").Column To Columns("H").Column
            Set r = Range(Cells(5, cini), Cells(5, cfin))
            Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)
            If Not b Is Nothing Then
                Cells(i, b.Column).Value = Cells(i, j).Value
                cini = b.Column
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Regards Dante Amor

 

[motilulla]

Is it possible that you have errors in your example? p.e on line 8


G8 = 2, the first 2 into header K5:X5 is S5, Then, the split must be:

	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
5				1	1	1		4		2		2	2	1
8				1	1					2		2

<TBODY>
</TBODY>

DanteAmor, sorry, yes you are correct it was my fault while doing it manually

If the above is correct, run this macro:

Sub Split_Number()
'
    Application.ScreenUpdating = False
    u = Range("C" & Rows.Count).End(xlUp).Row
    Range("K6:X" & u).ClearContents
    For i = 6 To u
        cini = Columns("J").Column
        cfin = Columns("X").Column
        For j = Columns("C").Column To Columns("H").Column
            Set r = Range(Cells(5, cini), Cells(5, cfin))
            Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)
            If Not b Is Nothing Then
                Cells(i, b.Column).Value = Cells(i, j).Value
                cini = b.Column
            End If
        Next
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Regards Dante Amor

DanteAmor, your VBA code worked at first glance 100% perfect! Thank you very much for the help!</SPAN></SPAN>

Query solved! </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[DanteAmor]

Im glad to helo you

 

[motilulla]

Hello, as far as code is in the post#2 it is working properly. But I needed a bit change and it stop working can some tell me why</SPAN></SPAN>

I had values in numbers K5:X5 the change I have done values put in to cells A6:A19 and the formula in K5=A6, in L5=A7 and so on up to X5 when I run the code I do not get results in the K6:X52 </SPAN></SPAN>

Please can some one take a look in it?</SPAN></SPAN>

The example....</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4	Num		n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6	n7	n8	n9	n10	n11	n12	n13	n14
5	Num		n1	n2	n3	n4	n5	n6			0	0	1	1	1	0	4	0	2	0	2	2	1	0
6	0		0	0	1	1	0	4
7	0		0	0	1	1	4	0
8	1		0	0	1	1	2	2
9	1		0	0	1	0	4	1
10	1		0	0	1	4	0	1
11	0		0	0	1	4	1	0
12	4		0	0	1	2	2	1
13	0		0	0	0	4	0	2
14	2		0	0	0	4	2	0
15	0		0	0	0	2	2	2
16	2		0	0	4	0	2	0
17	2		0	0	4	0	0	2
18	1		0	0	4	2	0	0
19	0		0	0	2	0	2	2
20			0	0	2	2	2	0
21			0	1	1	1	2	1
22			0	1	1	0	4	0
23			0	1	1	0	2	2
24			0	1	1	4	0	0
25			0	1	1	2	0	2
26			0	1	1	2	2	0
27			0	1	0	4	0	1
28			0	1	0	4	1	0
29			0	1	0	2	2	1
30			0	1	4	0	0	1
31			0	1	4	0	1	0
32			0	1	2	0	2	1
33			0	1	2	2	1	0
34			0	4	0	2	0	0
35			0	4	0	0	2	0
36			0	2	0	2	2	0
37			1	1	1	0	2	1
38			1	1	1	2	0	1
39			1	1	1	2	1	0
40			1	1	0	4	0	0
41			1	1	0	0	2	2
42			1	1	0	2	0	2
43			1	1	0	2	2	0
44			1	1	4	0	0	0
45			1	1	2	0	2	0
46			1	0	4	0	0	1
47			1	0	4	0	1	0
48			1	0	0	2	2	1
49			1	0	2	0	2	1
50			1	0	2	2	1	0
51			1	4	0	0	1	0
52			1	2	0	2	1	0
Sheet2

Thank you</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[DanteAmor]

Change this line:

Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)

By:

Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole, lookin:=xlvalues)

 

[motilulla]

Change this line:

Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)

By:

Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole, lookin:=xlvalues)

DanteAmor, how nice spot on code line work like a magic</SPAN></SPAN>

Thank you so much</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>