Splitting column of numbers into multiple columns moving to new column based on value

Pk24

New Member
Joined
Jul 5, 2012
Messages
9
Hi


I have a spreadsheet with store numbers in column A , and units in column B


I need to split this into batches of new columns adding up the units in B and moving to new column when value is equal or greater than


Eg

A. B

601 1
602. 2
603. 3
604 4
605. 5
606. 1
607. 3



If I set value to check for as 4. Would become (ignore dots)

C. ...D. ..E. .F. ..G. .H. I. ..J

601. 1. 604. 4. 605. 5. 606. 1
602. 2. ......................607. 3
603. 3


Can anyone point me in the right direction macro etc



Thanks in advance



Pk24
 
Last edited:

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Welcome to Board!

Try this:
Code:
Sub Redistr()
chk = 4  'check value. can use Inputbox to get it
lr = Cells(Rows.Count, "A").End(xlUp).Row 'last row in column A
bsum = 0
nxtcol = 3
nxtrow = 1
For r = 1 To lr
    Cells(nxtrow, nxtcol) = Cells(r, "A")
    Cells(nxtrow, nxtcol + 1) = Cells(r, "B")
    nxtrow = nxtrow + 1
    bsum = bsum + Cells(r, "B")
    If bsum > chk Then
        nxtcol = nxtcol + 2
        nxtrow = 1
        bsum = 0
    End If
Next r
End Sub
 

Pk24

New Member
Joined
Jul 5, 2012
Messages
9
Thanks for this and the welcome works great , but i need to complicate things a bit more


I need when it splits to check the total of the previous units if above a certain qty to move the last data to next column , if equal or below to duplicate last data on bottom of current column to top of next column unless it is last data

Ie. if original check was 4 I'd get these results

C. ...D. ..E. .F. ..G. .H. I. ..J

601. 1. 604. 4. 605. 5. 606. 1
602. 2. ......................607. 3
603. 3

But after a second check on the total of previous data for 3
Would return

C. ...D. ..E. .F. ..G. .H. I. ..J. K. L

601. 1. 603. 3. 604. 4. 605. 5. 606. 1
602. 2. 604. 4...................... .607. 3
603. 3


Hope this makes sense


Cheers

Pk24
 
Last edited:

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Did you actually run the code??
It does exactly that.
As to changing the check value from 4 to 3, you can either change it in the code itself or use an InputBox to get it programmatically.
Thus:

Code:
Sub Redistr()
chk = CInt(InputBox("Enter Check Value", "Redistr"))
If Not IsNumeric(chk) Then Exit Sub 'exit if NOT a number.
lr = Cells(Rows.Count, "A").End(xlUp).Row 'last row in column A
bsum = 0
nxtcol = 3
nxtrow = 1
For r = 1 To lr
    Cells(nxtrow, nxtcol) = Cells(r, "A")
    Cells(nxtrow, nxtcol + 1) = Cells(r, "B")
    nxtrow = nxtrow + 1
    bsum = bsum + Cells(r, "B")
    If bsum > chk Then
        nxtcol = nxtcol + 2
        nxtrow = 1
        bsum = 0
    End If
Next r
End Sub
 

Pk24

New Member
Joined
Jul 5, 2012
Messages
9
I think u misunderstand, the code you supplied does the first stage but I need it to check the qty of the ones above the split to determine what to do with the split data ( wether to duplicate it bottom of current column and top of next column , or just to put on top of next column )


For example let's say it was a pack of 6 eggs and u do a check for 6 eggs

The check determines on line 5 we have 8 eggs so would move to next box , but a further check determines we have only 5 eggs on line 4 , so would duplicate line 5 , 1 egg in first box and remainder would start of box 2

But if the further check returned that there was in fact 6 eggs on line 4 , it would only show line 5 at top of next column ( new box)


Hence your code only solves part of my problem


Cheers.



Pk24


Did you actually run the code??
It does exactly that.
As to changing the check value from 4 to 3, you can either change it in the code itself or use an InputBox to get it programmatically.
Thus:

Code:
Sub Redistr()
chk = CInt(InputBox("Enter Check Value", "Redistr"))
If Not IsNumeric(chk) Then Exit Sub 'exit if NOT a number.
lr = Cells(Rows.Count, "A").End(xlUp).Row 'last row in column A
bsum = 0
nxtcol = 3
nxtrow = 1
For r = 1 To lr
    Cells(nxtrow, nxtcol) = Cells(r, "A")
    Cells(nxtrow, nxtcol + 1) = Cells(r, "B")
    nxtrow = nxtrow + 1
    bsum = bsum + Cells(r, "B")
    If bsum > chk Then
        nxtcol = nxtcol + 2
        nxtrow = 1
        bsum = 0
    End If
Next r
End Sub
 

Pk24

New Member
Joined
Jul 5, 2012
Messages
9
heres my file so far

Code:
https://rapidshare.com/files/2757403898/HDPLANNER.xlsm
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

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
Top