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.

 

[venkat1926]

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

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

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

 

[Peter_SSs]

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>

 

[MattsDad]

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.

 

[ian_ticketserv]

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

 

[Peter_SSs]

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.

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
	A	B	C	D	E	F	G	H	I
1	Desc	Barcode	Price	Qty
2	Desc 1	BC 1	8	3		Desc 1	BC 1	8	3
3	Desc 2	BC 2	6	1		Desc 1	BC 1	8	3
4	Desc 3	BC 3	7	4		Desc 1	BC 1	8	3
5						Desc 2	BC 2	6	1
6						Desc 3	BC 3	7	4
7						Desc 3	BC 3	7	4
8						Desc 3	BC 3	7	4
9						Desc 3	BC 3	7	4
10
Convert to Multiple rows

 

[MelRose]

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:

Store	Product	Date	Store	Sales
GSP	A	6/7/16	GSP	3
GSP	B	6/5	GSP	1

<tbody>
</tbody>

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!!

 

[Peter_SSs]

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?

 

[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

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

Convert to Multiple rows

	A	B	C	D	E	F	G	H	I
1	Desc	Barcode	Price	Qty
2	Desc 1	BC 1	8	3		Desc 1	BC 1	8	3
3	Desc 2	BC 2	6	1		Desc 1	BC 1	8	3
4	Desc 3	BC 3	7	4		Desc 1	BC 1	8	3
5						Desc 2	BC 2	6	1
6						Desc 3	BC 3	7	4
7						Desc 3	BC 3	7	4
8						Desc 3	BC 3	7	4
9						Desc 3	BC 3	7	4
10

<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>
</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

 

[Peter_SSs]

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.