Split 6 number in to 14 cells as per order finds

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
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
ABCDEFGHIJKLMNOPQRSTUVWX
1
2
3
4n1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14
5n1n2n3n4n5n600111040202210
6001104001104
7001140001140
8001122001122
9001041001041
10001401001401
11001410001410
12001221001221
13000402000402
14000420000420
15000222000222
16004020004020
17004002004002
18004200004200
19002022002022
20002220002220
21011121011121
22011040011040
23011022011022
24011400011400
25011202011202
26011220011220
27010401010401
28010410010410
29010221010221
30014001014001
31014010014010
32012021012021
33012210012210
34040200040200
35040020040020
36020220020220
37111021111021
38111201111201
39111210111210
40110400110400
41110022110022
42110202110202
43110220110220
44114000114000
45112020112020
46104001104001
47104010104010
48100221100221
49102021102021
50102210102210
51140010104010
52120210120210
Sheet2


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN></SPAN>
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If the above is correct, run this macro:

Code: 
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
 
Last edited:
Upvote 0
DanteAmor said:
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:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
DanteAmor, sorry, yes you are correct it was my fault while doing it manually

DanteAmor said:
If the above is correct, run this macro:

Code: 
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
Click to expand...
DanteAmor, your VBA code worked at first glance 100% perfect! Thank you very much for the help!</SPAN></SPAN>

Query solved! :beerchug:</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti :grin:</SPAN></SPAN>
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWX
1
2
3
4Numn1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14
5Numn1n2n3n4n5n600111040202210
60001104
70001140
81001122
91001041
101001401
110001410
124001221
130000402
142000420
150000222
162004020
172004002
181004200
190002022
20002220
21011121
22011040
23011022
24011400
25011202
26011220
27010401
28010410
29010221
30014001
31014010
32012021
33012210
34040200
35040020
36020220
37111021
38111201
39111210
40110400
41110022
42110202
43110220
44114000
45112020
46104001
47104010
48100221
49102021
50102210
51140010
52120210
Sheet2


Thank you</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
Change this line:
Code: 
Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)

By:
Code: 
Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole, lookin:=xlvalues)
 
Upvote 0
Solution
DanteAmor said:
Change this line:
Code: 
Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole)

By:
Code: 
Set b = r.Find(Cells(i, j).Value, lookat:=xlWhole, lookin:=xlvalues)
Click to expand...
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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

serge
  • Question Question
#N/A cell skip.
Replies
3
Views
310
Peter_SSs
Peter_SSs
K
  • Solved
WHEN vba finds Row in header then places row number below it
Replies
5
Views
308
KDS14589
K
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
U
  • Solved
VBA To Add rows to TABLE based on a cells value
Replies
3
Views
257
igold
igold
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
291
RobP
R

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top