Convert single PO line item to multiple rows based on quantity ordered

[MattsDad]

Hi. I'm trying to build a process to aid our warehouse in printing barcode labels for inventory received. Currently someone has to take the PO/receiving document and manually enter the part number into a text file on a separate row for each individual piece that is received so our barcode printer can print the correct label to attach to our product.

For example, on a basic receiving document we receive 3 units of Item A and 1 unit of Item B that shows like this:
Item Qty
A 3
B 1

The warehouse worker then enters the data like this in the text file that will be printed:
A
A
A
B

The question is, can i do this in Excel 2003 on an XP box? We can easily import this data from the PO/receiving report to Excel, which I have done. I'm trying to figure out how to get Excel to create an output file either directly to a text file or to another tab in the workbook but I don't know how to instruct Excel to use one line of input as a source for more than one line of output.

I've spent several hours trying formulas to no avail. I don't know enough about VBA code to be able to write this myself. Any help you can give will be greatly appreciated.

 

[amora1012]

Welcome to the MrExcel board!

I'm not really sure what you are asking.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, just quote small, relevant parts only.

thanks peter for your quick answer i just need to know how i can make this VBA code live on excel 2016 as mentioned above and sorry for long quote

 

[Peter_SSs]

thanks peter for your quick answer i just need to know how i can make this VBA code live on excel 2016 ..

The code works for me on Excel 2016 as it is. What problems are you having?
Have you tried it on a fresh sheet with the sample data as shown in columns A:D of post 6? Does it work for you for that data?

 

[amora1012]

its worked but im trying to make it on my data so can i give you it and make it for me please ?

 

[Peter_SSs]

its worked but im trying to make it on my data so can i give you it and make it for me please ?

You are welcome to post a small set of your dummy data and expected results so we can see what you have and what you are trying to achieve. My signature block below has a link for how to do that.

 

[amora1012]

You are welcome to post a small set of your dummy data and expected results so we can see what you have and what you are trying to achieve. My signature block below has a link for how to do that.

thanks peter so my orders is,

ORDER_NUMBER	SKU	DESCRIPTION	ORDERED_QUANTITY	DELIVERY_METHOD
5004811	112170313	iPhone 7 32GB Matte Black Local	2	MovEx
5004828	112170379	IPhone 6 32GB Grey LTE/ Local	2	ELT Driver
5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	2	Sales
5004714	112170402	Iphone X 256 GB Silver /Local	2	NokSmart
5004710	112170404	IPhone X 256 GB Space Gray/Local	2	Union Courier

<tbody>
</tbody>

i need to distributed into that example,

Order	SKU	Item Description	QTY	Serial Numbers	18 Digit	SR Number	Released By	Delivery Method	Order Status
5004811	112170313	iPhone 7 32GB Matte Black Local	1					MovEx
	112170313	iPhone 7 32GB Matte Black Local	1					MovEx
5004828	112170379	IPhone 6 32GB Grey LTE/ Local	1					ELT Driver
	112170379	IPhone 6 32GB Grey LTE/ Local	1					ELT Driver
5004845	112170387	iPhone 8 Plus 64GB Space Grey /Local	1					Sales
	112170387	iPhone 8 Plus 64GB Space Grey /Local	1					Sales
5004714	112170402	Iphone X 256 GB Silver /Local	1					NokSmart
	112170402	Iphone X 256 GB Silver /Local	1					NokSmart
5004710	112170404	IPhone X 256 GB Space Gray/Local	1					Union Courier
	112170404	IPhone X 256 GB Space Gray/Local	1					Union Courier

<tbody>
</tbody>

​

<tbody>
</tbody>

 

[amora1012]

Welcome to the MrExcel board!

My best guess, without seeing a small set of sample data and expected results is as follows.
Test in a copy of your workbook.

If it is not what you want, please give a small set of sample data and expected results together with further explanation. My signature block below has further info about how to do screen shots.

Sub Rearrange()
  Dim a, b
  Dim i As Long, j As Long, k As Long, z As Long
  
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To 4, 0 To 0)
    For i = 1 To UBound(a)
      ReDim Preserve b(1 To 4, 1 To UBound(b, 2) + a(i, 4))
      For j = 1 To a(i, 4)
        z = z + 1
        For k = 1 To 4
          b(k, z) = a(i, k)
        Next k
      Next j
    Next i
    .Offset(, .Columns.Count + 1).Resize(z).Value = Application.Transpose(b)
  End With
End Sub

around this code please can you tell me how can i make this Red Function?
thank you

 

[Peter_SSs]

Try this code in a copy of your workbook.

Sub Rearrange()
  Dim a, b
  Dim i As Long, j As Long, k As Long, z As Long, rws As Long, cols As Long
  
  With Range("A2", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    rws = Application.WorksheetFunction.Sum(.Columns(4))
    cols = UBound(a, 2)
    ReDim b(1 To rws, 1 To cols)
    For i = 1 To UBound(a)
      For k = 1 To a(i, 4)
        z = z + 1
        For j = 1 To cols
            b(z, j) = a(i, j)
        Next j
      Next k
    Next i
    With .Offset(, .Columns.Count + 1).Resize(rws)
      .Value = b
      .Columns(5).Resize(, 4).Insert
      .Cells(0, 1).Resize(, 10).Value = Split("Order|SKU|Item Description|QTY|Serial Numbers|18 Digit|SR Number|Released By|Delivery Method|Order Status", "|")
      .Columns(4).Value = 1
      .CurrentRegion.Columns.AutoFit
    End With
  End With
End Sub

 

[amora1012]

How can i post it on my workbook ? i put it as a module 1 or what ?

 

[Peter_SSs]

How can i post it on my workbook ? i put it as a module 1 or what ?

Hmm, in post 13 you said the previous code "worked" so I assumed you knew how to implement the code.

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[amora1012]

many thanks for your usual support peter you're really polite man and im sorry for disturbing you many times so ive another question can i do this functions with macro codes ? for example with offset function with any formulas ?