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

MattsDad

New Member
Joined
Sep 1, 2010
Messages
14
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Have I understood correctly.

try this macro on the sample data and if ok use it for your sheet.
macro undo will remove the result of the macro

Code:
Sub test()
Dim r As Range, j As Integer, k As Integer, m As Integer
Dim dest As Range
 j = Range("A1").End(xlDown).Row
Set dest = Range("A1").End(xlDown).Offset(5, 0)
For k = 2 To j
m = Cells(k, 2)
Range(dest, dest.Offset(m - 1, 0)) = Cells(k, 1)
If k = j Then GoTo eend
Set dest = dest.End(xlDown).Offset(1, 0)
Next k
eend:
End Sub

Code:
Sub undo()
Dim j As Integer
j = Range("A1").End(xlDown).Row
Range(Cells(j + 1, 1), Cells(Rows.Count, 1)).EntireRow.Delete
End Sub
 
Upvote 0
Not sure where your data is or where you want the results but for data in A1:Bxx and results in column D, here is another version.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Create_Multiples()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("A2", Range("A" & Rows.Count).End(xlUp))<br>        rws = c.Offset(, 1).Value<br>        <SPAN style="color:#00007F">If</SPAN> rws > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(rws).Value = c.Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br></FONT>
 
Upvote 0
Peter_SSs / venkat1926:

Thank you both for the quick replies and time you spent working on my problem. I tried both macros but was only able to get Peter's to work on my computer. It works exactly as I had hoped.
 
Upvote 0
Hi Peter
greetings
I just came across this post after trying to do something similar to the original post outline.
I tried your script and worked brilliantly.
My need is to also have some extra data extended into columns as well. The source data is (in columns):
Col A: item description
Col B: Item barcode
Col C: Item price
Col D: Item quantity
And the extended data needs to be:
Col A: item description
Col B: Item barcode
Col C: Item price
Col D: 1 (being one unique line for each in total)

Appreciate this is an old post but hopefully still able to assist
Thanks in advance
Ian
 
Upvote 0
My need is to also have some extra data extended into columns as well. The source data is (in columns):
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.

Rich (BB code):
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


For my sample data, original data in columns A:D, code results in columns F:I

Excel Workbook
ABCDEFGHI
1DescBarcodePriceQty
2Desc 1BC 183Desc 1BC 183
3Desc 2BC 261Desc 1BC 183
4Desc 3BC 374Desc 1BC 183
5Desc 2BC 261
6Desc 3BC 374
7Desc 3BC 374
8Desc 3BC 374
9Desc 3BC 374
10
Convert to Multiple rows
 
Upvote 0
Hi Peter,
I was wondering if you could help me as well...this macro seems to be exactly what I need, but I am having trouble configuring it to my data, as I am not that familiar with VBA.

So my sample data is:

[TABLE="class: outer_border, width: 100"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Product[/TD]
[TD]Date[/TD]
[TD]Store[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]GSP[/TD]
[TD]A[/TD]
[TD]6/7/16[/TD]
[TD]GSP[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]GSP[/TD]
[TD]B[/TD]
[TD]6/5[/TD]
[TD]GSP[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I need each individual sale by each store for each day per product on separate lines. Could you help me write this out?

Thank you so much!!
 
Upvote 0
Welcome to the MrExcel board!

I need each individual sale by each store for each day per product on separate lines.
You showed your sample data but only described the expected result. Could you show us the expected result as well please?
 
Upvote 0
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.

Rich (BB code):
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


For my sample data, original data in columns A:D, code results in columns F:I

Convert to Multiple rows

ABCDEFGHI
DescBarcode
Desc 1BC 1 Desc 1BC 1
Desc 2BC 2 Desc 1BC 1
Desc 3BC 3 Desc 1BC 1
Desc 2BC 2
Desc 3BC 3
Desc 3BC 3
Desc 3BC 3
Desc 3BC 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:60px;"><col style="width:67px;"><col style="width:49px;"><col style="width:40px;"><col style="width:18px;"><col style="width:60px;"><col style="width:48px;"><col style="width:26px;"><col style="width:26px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Price[/TD]
[TD="align: right"]Qty[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Thanks peter for your usual support and im kindly asking you about i need know what the functions are used to do it and if you can show me what you doing to make this mentioned above VBA code thanks
 
Upvote 0
Thanks peter for your usual support and im kindly asking you about i need know what the functions are used to do it and if you can show me what you doing to make this mentioned above VBA code thanks
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.
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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
Back
Top