Splitting Lines Based on Number of Units and Cost

  • so I am trying to split excel cells into multiple lines based on number of units and Cost. Illustration below


    How it Is
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 144"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]Fruit[/TD]
    [TD="width: 64, bgcolor: transparent"]Units[/TD]
    [TD="width: 64, bgcolor: transparent"]Total Cost[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Apple[/TD]
    [TD="bgcolor: transparent, align: right"]2[/TD]
    [TD="bgcolor: transparent, align: right"]100[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Orange [/TD]
    [TD="bgcolor: transparent, align: right"]3[/TD]
    [TD="bgcolor: transparent, align: right"]200[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Guava[/TD]
    [TD="bgcolor: transparent, align: right"]4[/TD]
    [TD="bgcolor: transparent, align: right"]600[/TD]

    [/tr]


    [/TABLE]


    How I Want it
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 144"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]Fruit[/TD]
    [TD="width: 64, bgcolor: transparent"]Units[/TD]
    [TD="width: 64, bgcolor: transparent"]Cost Per unit[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Apple[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]50[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Apple[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]50[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Orange [/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]66.67[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Orange [/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]66.67[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Orange [/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]66.67[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Guava[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]150[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Guava[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]150[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Guava[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]150[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]Guava[/TD]
    [TD="bgcolor: transparent, align: right"]1[/TD]
    [TD="bgcolor: transparent, align: right"]150[/TD]

    [/tr]


    [/TABLE]


    I have a VBA which works but I don't know how to apply. Need help with that. VBA that works is as below




    Sub test()
    Dim lastrow As Integer
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim howmany As Integer
    For y = lastrow To 1 Step -1
    If Cells(y, 11) > 1 Then
    howmany = Cells(y, 11)
    For v = 1 To howmany - 1
    Rows(y + 1).Insert (xlShiftDown)
    Cells(y, 11) = 1
    Cells(y + 1, 1) = Cells(y, 1)
    Cells(y + 1, 2) = Cells(y, 2)
    Cells(y + 1, 3) = Cells(y, 3)
    Cells(y + 1, 4) = Cells(y, 4)
    Cells(y + 1, 5) = Cells(y, 5)
    Cells(y + 1, 6) = Cells(y, 6)
    Cells(y + 1, 7) = Cells(y, 7)
    Cells(y + 1, 8) = Cells(y, 8)
    Cells(y + 1, 9) = Cells(y, 9)
    Cells(y + 1, 10) = Cells(y, 10)
    Cells(y + 1, 11) = Cells(y, 11)
    Cells(y + 1, 12) = Cells(y, 12)
    Cells(y + 1, 13) = Cells(y, 13)
    Cells(y + 1, 14) = Cells(y, 14)
    Next
    End If
    Next


    End Sub

  • Assuming your data is in "Sheet1", make this the active sheet and run this macro. Your result will be place in "Sheet2".

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Sure if a formula works that just as good, it does not have to be VBA


    Find the following dynamic named ranges in Name Manager:

    [Table="class: grid"]

    [tr][td]

    Fruit

    [/td][td]

    =Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH("zzzzzz",Sheet1!$A:$A,1))

    [/td][/tr]


    [tr][td]

    Total_Cost

    [/td][td]

    =Sheet1!$C$3:INDEX(Sheet1!$C:$C,MATCH(1E+306,Sheet1!$C:$C,1))

    [/td][/tr]


    [tr][td]

    Units

    [/td][td]

    =Sheet1!$B$3:INDEX(Sheet1!$B:$B,MATCH(1E+306,Sheet1!$B:$B,1))

    [/td][/tr]


    [/table]


    Then in E3 and filled down

    Code
    =IF(ROWS(E$3:E3)>SUM(Units),"",INDEX(Fruit,MATCH(1,INDEX(--(ROWS($1:1)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)))


    In F3 and filled down

    Code
    =IF(ROWS(E$3:E3)>SUM(Units),"",1)


    And in G3 and filled down

    Code
    =IF(ROWS(E$3:E3)>SUM(Units),"",ROUND(INDEX(Total_Cost,MATCH(1,INDEX(--(ROWS($1:1)<=
    SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0))/
    INDEX(Units,MATCH(1,INDEX(--(ROWS($1:1)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)),2))
  • Really Appreciate the effort on the formula [USER="306542"]FlameRetired[/USER] the thing is that I have many columns in between those and the data set is large, I tried the formula it works perfectly fine but how do I cater for all the other columns as I want them to be exactly the same as they are in the original cell, for example a supplier or the purchase order number.

  • Without a representative data set that reflects the dilemma I can't say. However it sounds like it would be a simple matter of changing the relative column ranges in the formula. If the header labels are exactly the same I wouldn't think that would be a problem.


    With the exception of the column of 1's all the formulas are driven by the formula component:


    MATCH(1,INDEX(--(ROWS(E$3:E3)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)


    It always returns each item in the 'Fruit', 'Units' and 'Total Cost' n number of Units times.


    However:

    Quote

    ... the data set is large


    You hadn't mentioned that before. That would be the only potential problem as I see from the description. As OFFSET is a volatile function too many of them can slow a workbook down. Up to about 1000 cells this is usually not a problem.


    In numbers ... rows and columns ... how large is the anticipated output range?

  • It would be easier to help if we could see what your actual data looks like. Could you attach a copy of your file?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Yes [USER="306542"]FlameRetired[/USER] the sheet has around 30-40k rows. Thanks for the help though I used the original VBA that I had shared and manipulated it a few times to get the desired result, I tried the formula but it was almost every time slowing the sheet down.


    I got the desired result form the original VBA that I shared, I was able to split the lines based on the number of units.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!