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

[Pk24]

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

 

[drsarao]

Welcome to Board!

Try this:

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]

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

 

[drsarao]

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:

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]

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:

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]

heres my file so far

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