Assistance with separating by a pack qty

[Davealot]

Greetings my friends,
I have unfortunately came to request help yet again. I've attached a workbook with three separate sheets in it. The first sheet is an order that we have, the third sheet is what I'm wanting it to look like. If you'll notice on the first sheet there is a "QTY" column which is the qty on order and the column titled "Pack Qty." is the amount that goes into the order per box. What I am trying to accomplish is to have it duplicate by the pack qty to the sheet titled "Output" based on the amount that goes in each box, if possible. For example the first part number there are 4 on order, and it's a pack quantity of 1 so it would make four entries and the second part is packed by 5 with 20 on order, so four boxes of 5. I'm setting this up for automated label printing which is why I'm wanting it to handle the simple division, I'm just not really sure how to accomplish said task. As usual any and all help is greatly appreciated.

 

[vletm]

something like this
... press [Do it]

 

[Chihiro]

Do you have access to PowerQuery?

If you do, this operation is fairly simple. Just need to set it up once and no need for code.

Convert data to table (table1) and load as connection only.

Create table (table2) of numbers which repeats itself based on number (i.e. 1 = 1 row, 2 = 2 row .... so on so forth) and load as connection only. For example I've created table for number up to 10.

Now in PowerQuery, add custom column to table1. QTY column divided by Pack Qty. column.

Next go to table2 in PowerQuery and merge with table1 using custom column. You will be using "Inner join".

Expand joined table and rearrange/remove columns. Load result to worksheet.

You can refresh the result and will update if any info is changed or added to original.

See attached for reference.

 

[Davealot]

something like this
... press [Do it]

Excellent, Thank you kindly!

 

[Davealot]

Do you have access to PowerQuery?

If you do, this operation is fairly simple. Just need to set it up once and no need for code.

Convert data to table (table1) and load as connection only.

Create table (table2) of numbers which repeats itself based on number (i.e. 1 = 1 row, 2 = 2 row .... so on so forth) and load as connection only. For example I've created table for number up to 10.

Now in PowerQuery, add custom column to table1. QTY column divided by Pack Qty. column.

Next go to table2 in PowerQuery and merge with table1 using custom column. You will be using "Inner join".

Expand joined table and rearrange/remove columns. Load result to worksheet.

You can refresh the result and will update if any info is changed or added to original.

See attached for reference.

Unfortunately no, at this time I do not have access to powerquery, although we are investigating internally of converting all of our users to office 365. I'm hopeful we do as that would make this much easier, I agree!

 

[Davealot]

something like this
... press [Do it]

Vlet, Quick question can you explain how the "o_tab" works? I've never seen that used before, thanks!

 

[vletm]

oooo ...
o_tab = "Output"
if need to write many times sheets("Output") or sheets(o_tab)
I would use sheets(o_tab) ... shorter way and
if 'tab'-name is looong (like Your 3rd tab's name),
shorter o_tab at least 'looks better'.
Ideas?

 

[Davealot]

oooo ...
o_tab = "Output"
if need to write many times sheets("Output") or sheets(o_tab)
I would use sheets(o_tab) ... shorter way and
if 'tab'-name is looong (like Your 3rd tab's name),
shorter o_tab at least 'looks better'.
Ideas?

Reason I'm asking is I'm receiving this error message now when I try to apply to my main workbook, I gave an example of just a piece of the main workbook as some of what I have is company confidential, see attached.

 

[vletm]

Option Explicit -case
You're using that and
then You have to declare all variables, not only that o_tab.

 

[Davealot]

Option Explicit -case
You're using that and
then You have to declare all variables, not only that o_tab.

........I'm stupid. Thanks, lol

 

[Davealot]

Option Explicit -case
You're using that and
then You have to declare all variables, not only that o_tab.

One final question and I'll try to leave you alone, If I add another column before the "Pack Qty." column what changes would I need to make in the code to get it to add that column into the final "Output" sheet? I realized I left a column out and tried to add it before the pack qty. column and I've made a mess and not sure with my limited knowledge how to correct, thank you.

 

[Chihiro]

Few things to change in vletm's code.

Anywhere you see "A1:H" change to "A1:I".

d_row(9) should be changed to d_row(10)

"x = 1 to 9" changed to "x = 1 to 10"

"x = 1 to 8" changed to "x = 1 to 9"

 

[jindon]

Try
You need to adjust QTY col reference when it is not col.D.(4)

Sub test()
    Dim a, b, i As Long, ii As Long, t As Long, n As Long
    Const Qty As Long = 4
    With Sheets("order").Cells(1).CurrentRegion
        a = .Value
        ReDim b(1 To Application.Sum(.Columns(Qty)), 1 To UBound(a, 2) - 1)
    End With
    For i = 2 To UBound(a, 1)
        For t = 1 To a(i, Qty) / a(i, UBound(a, 2))
            n = n + 1
            For ii = 1 To UBound(a, 2) - 1
                b(n, ii) = IIf(ii = Qty, a(i, UBound(a, 2)), a(i, ii))
    Next ii, t, i
    With Sheets("output").Cells(1).Resize(n, UBound(a, 2) - 1)
        .CurrentRegion.Borders.LineStyle = xlNone: .Borders.Weight = 2
        .Value = b: .Columns.AutoFit: .Parent.Select
    End With
End Sub

 

[vletm]

@Davealot
My 2nd version of my 1st version.
You didn't tell 'where' do You want that 'NEW' to 'Output'.
This adds it to the most right.
It could add 'anywhere' else too, with some modifications.
Q: Why did You wrote I'll try to leave you alone ... ? What to do with my pets ?