Creating duplicate rows based off of quantity in cell

R

russborup

New Member
Joined
May 2, 2018
Messages
23
Hello all!
I have data that is sent in where a QTY (Column C) is populated, but the information in that row must be populated as many times as the quantity in that row.
Example:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl65, width: 64, align: center"]A[/TD]
[TD="class: xl65, width: 64, align: center"]B[/TD]
[TD="class: xl65, width: 64, align: center"]C[/TD]
[TD="class: xl65, width: 64, align: center"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]PO[/TD]
[TD="class: xl65, align: center"]Model[/TD]
[TD="class: xl65, align: center"]Qty[/TD]
[TD="class: xl65, align: center"]Std_Item[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl65, align: center"]RCB713[/TD]
[TD="class: xl65, align: center"]XWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14X[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl65, align: center"]RCB695[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl65, align: center"]RCB694[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl65, align: center"]RCB631[/TD]
[TD="class: xl65, align: center"]XROLLER[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]9X[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]6[/TD]
[TD="class: xl65, align: center"]RCB613[/TD]
[TD="class: xl65, align: center"]YSTAND[/TD]
[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl65, align: center"]98[/TD]
[/TR]
</tbody>[/TABLE]

Result needed:
[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl66, width: 64, align: center"][/TD]
[TD="class: xl65, width: 64, align: center"]A[/TD]
[TD="class: xl65, width: 64, align: center"]B[/TD]
[TD="class: xl65, width: 64, align: center"]C[/TD]
[TD="class: xl65, width: 64, align: center"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]PO[/TD]
[TD="class: xl65, align: center"]Model[/TD]
[TD="class: xl65, align: center"]Qty[/TD]
[TD="class: xl65, align: center"]Std_Item[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]2[/TD]
[TD="class: xl65, align: center"]RCB713[/TD]
[TD="class: xl65, align: center"]XWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14X[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]3[/TD]
[TD="class: xl65, align: center"]RCB695[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]4[/TD]
[TD="class: xl65, align: center"]RCB695[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]5[/TD]
[TD="class: xl65, align: center"]RCB695[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]6[/TD]
[TD="class: xl65, align: center"]RCB695[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]7[/TD]
[TD="class: xl65, align: center"]RCB694[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]8[/TD]
[TD="class: xl65, align: center"]RCB694[/TD]
[TD="class: xl65, align: center"]ZWING[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]14Z[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]9[/TD]
[TD="class: xl65, align: center"]RCB631[/TD]
[TD="class: xl65, align: center"]XROLLER[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]9X[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]10[/TD]
[TD="class: xl65, align: center"]RCB613[/TD]
[TD="class: xl65, align: center"]YSTAND[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]98[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]11[/TD]
[TD="class: xl65, align: center"]RCB613[/TD]
[TD="class: xl65, align: center"]YSTAND[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]98[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]12[/TD]
[TD="class: xl65, align: center"]RCB613[/TD]
[TD="class: xl65, align: center"]YSTAND[/TD]
[TD="class: xl65, align: center"]1[/TD]
[TD="class: xl65, align: center"]98[/TD]
[/TR]
</tbody>[/TABLE]

I do not necessarily need to have the column populated in the reults.
 
Sort by date Sort by votes

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Fluff
It worked great with the original data

I tried new data

[TABLE="width: 528"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PO[/TD]
[TD]Model[/TD]
[TD]Qty[/TD]
[TD]Item[/TD]
[TD="align: center"]Color[/TD]
[/TR]
[TR]
[TD]0NB247[/TD]
[TD]ZWING[/TD]
[TD]12[/TD]
[TD]14Z[/TD]
[TD]3 BLK 3 GRY 3 BLU 3 SVR[/TD]
[/TR]
[TR]
[TD]0NB311[/TD]
[TD]ZWING[/TD]
[TD]1[/TD]
[TD]14Z[/TD]
[TD]1 WHT[/TD]
[/TR]
[TR]
[TD]0NB595[/TD]
[TD]ZWING[/TD]
[TD]1[/TD]
[TD]14Z[/TD]
[TD]1=BLK=BLACK[/TD]
[/TR]
[TR]
[TD]0NB308[/TD]
[TD]XWING[/TD]
[TD]15[/TD]
[TD]14X[/TD]
[TD]3 BLU 4 GRY 4 SVR 4 WHT[/TD]
[/TR]
</tbody>[/TABLE]

and I am getting the
Subscript out of range error
 
Upvote 0
How about
Code: 
Sub AddRws()
   Dim Ary As Variant, Nary As Variant, Sp As Variant
   Dim Qty As Long, r As Long, c As Long, i As Long
   Dim r2 As Long, k As Long
   
   Qty = Application.SUM(Columns(3))
   Ary = Range("A1").CurrentRegion.Offset(1).Value2
   ReDim Nary(1 To Qty, 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary)
      Sp = Split(Ary(r, 5))
      If InStr(1, Ary(r, 5), "=") = 0 Then
         For i = 0 To UBound(Sp) Step 2
            For k = 1 To Sp(i)
               r2 = r2 + 1
               For c = 1 To UBound(Ary, 2)
                  Nary(r2, c) = Ary(r, c)
               Next c
               Nary(r2, 3) = 1
               Nary(r2, 5) = 1 & " " & Sp(i + 1)
            Next k
         Next i
      Else
         For i = 1 To Ary(r, 3)
            r2 = r2 + 1
            For c = 1 To UBound(Ary, 2)
               Nary(r2, c) = Ary(r, c)
            Next c
            Nary(r2, 3) = 1
            Nary(r2, 5) = 1 & Mid(Ary(r, 5), InStr(1, Ary(r, 5), "="))
         Next i
      End If
   Next r
   Range("A1").CurrentRegion.Offset(1).ClearContents
   Range("A2").Resize(Qty, UBound(Nary, 2)).Value = Nary
End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Hello Fluff
The Macro you built has been working GREAT!
I did run into one issue where the color is coming up different for multiple colors, and it is not separating out.
Example:
[TABLE="width: 492"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PO[/TD]
[TD]Model[/TD]
[TD]Qty[/TD]
[TD]Std_Item[/TD]
[TD]Color[/TD]
[/TR]
[TR]
[TD]0NC070[/TD]
[TD]XWING[/TD]
[TD]2[/TD]
[TD]14Z[/TD]
[TD]1=BLU=BLUE 1=WHT=WHITE
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
130
JEC
JEC
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
250
SBExcel123
S
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
373
Amer Omar
A
E
  • Solved
Duplication of data onto new rows based on contents of cells which contain semi-colon separated entries
Replies
4
Views
491
ellison
E
T
  • Solved
Trying to Create a drop down list based on 2 columns matching
Replies
6
Views
451
TBud1981
T

Forum statistics

Threads
1,225,137
Messages
6,183,078
Members
453,146
Latest member
Lacey D

Share this page

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