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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
How about
Code: 
Sub AddRws()
   Dim Ary As Variant
   Dim Nary As Variant
   Dim Qty As Long, r As Long, c As Long, i As Long, r2 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)
      For i = 1 To Ary(r, 3)
         r2 = r2 + 1
         For c = 1 To UBound(Ary, 2)
            Nary(r2, c) = Ary(r, c)
            Nary(r2, 3) = 1
         Next c
      Next i
   Next r
   Range("A1").CurrentRegion.Offset(1).ClearContents
   Range("A2").Resize(Qty, UBound(Nary, 2)).Value = Nary
End Sub
 
Upvote 0
Fluff That worked perfectly!
Thank you very much!

There is one more piece to the puzzle for this if you interested.
Here is the same data, but you will notice in column E they give a color that I was going to do a delimited text to column to marry up to the results from the macro.
Can you think of a better way?

[TABLE="width: 537"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]RCB713[/TD]
[TD="align: center"]XWING[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]14X[/TD]
[TD]1 BLK[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]RCB695[/TD]
[TD="align: center"]ZWING[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14Z[/TD]
[TD]1 BLU 1 RED 1 WHT[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]RCB694[/TD]
[TD="align: center"]ZWING[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]14Z[/TD]
[TD]1 BLU 1 RED[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]RCB631[/TD]
[TD="align: center"]XROLLER[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9X[/TD]
[TD]1=BLK=BLACK[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]RCB613[/TD]
[TD="align: center"]YSTAND[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]98[/TD]
[TD]1 BLU 1 RED 1 WHT[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
On this row
[TABLE="class: cms_table, width: 537"]
<tbody>[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]RCB695[/TD]
[TD="align: center"]ZWING[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14Z[/TD]
[TD]1 BLU 1 RED 1 WHT[/TD]
[/TR]
</tbody>[/TABLE]

You have a value of 4 but only 3 items in col E.
Also where you have 1=BLK=BLACK what would that look like if the quantity was more than 1?
 
Upvote 0
Sorry for the row you showed, it cut off. it should be
[TABLE="class: cms_table_cms_table, width: 537"]
<tbody>[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]RCB695[/TD]
[TD="align: center"]ZWING[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14Z[/TD]
[TD]1 BLU 1 RED 1 WHT 1 GRY[/TD]
[/TR]
</tbody>[/TABLE]


When there is 1=BLK=BLACK, that is how it looks if there is only a quantity of 1.
 
Upvote 0
What would it be if there was a quantity of 3?
Or will you only get that for a quantity of 1?
 
Last edited:
Upvote 0
It would still be the same.
I will periodically get the color(s) with a = between them instead of the space but the number should still be there.
Example
[TABLE="width: 558"]
<tbody>[TR]
[TD="class: xl63, width: 60, align: center"]RCB425[/TD]
[TD="class: xl63, width: 92, align: center"]ZWING[/TD]
[TD="class: xl64, width: 75, align: center"]15[/TD]
[TD="class: xl64, width: 75, align: center"]14Z[/TD]
[TD="class: xl63, width: 256"]15=WHT=WHITE[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Is this what you want
Code: 
Sub AddRws()
   Dim Ary As Variant
   Dim Nary As Variant
   Dim Qty As Long, r As Long, c As Long, i As Long, r2 As Long, j As Long
   Dim Z As String
   
   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)
      j = 0
      For i = 1 To Ary(r, 3)
         r2 = r2 + 1
         For c = 1 To UBound(Ary, 2)
            Nary(r2, c) = Ary(r, c)
            Nary(r2, 3) = 1
         Next c
         If Not InStr(1, Ary(r, 5), "=") > 0 Then
            Nary(r2, 5) = Split(Ary(r, 5))(j) & " " & Split(Ary(r, 5))(j + 1)
            j = j + 2
         End If
      Next i
      
   Next r
   Range("A1").CurrentRegion.Offset(1).ClearContents
   Range("A2").Resize(Qty, UBound(Nary, 2)).Value = Nary
End Sub
 
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
247
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
449
TBud1981
T

Forum statistics

Threads
1,225,062
Messages
6,182,628
Members
453,128
Latest member
Dinesh Shukla

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