How do I duplicate lines based on a cell value

B

BazzaM

New Member
Joined
Nov 1, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am looking for some help in automating a copy on paste of a list of values, Repeating a specific line a number of times before moving to the next line.

An example of the input data would be

ABCDEFGHIJ
MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
Material 1
6200​
620​
2​
PC
2​
KG
18​
Description 1
2​
Material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 2
8​
Material 3
6200​
620​
8​
PC
8​
KG
1.84​
Description 3
4​

From this data, I would like to copy this data to a new sheet, I would like the lines repeating in the new sheet based on the value in "Labels Required" without the output looking like the below.

ABCDEFGHIJ
MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
Material 1
6200​
620​
2​
PC
2​
KG
18​
Description 11 of 2
Material 1
6200​
620​
2​
PC
2​
KG
18​
Description 12 of 2
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 21 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 22 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 23 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 24 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 25 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 26 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 27 of 8
material 2
6200​
620​
8​
PC
8​
KG
14.4​
Description 28 of 8
Material 3
6200​
620​
8​
PC
8​
KG
1.84​
Description 31 of 4
Material 3
6200​
620​
8​
PC
8​
KG
1.84​
Description 32 of 4
Material 3
6200​
620​
8​
PC
8​
KG
1.84​
Description 33 of 4
Material 3
6200​
620​
8​
PC
8​
KG
1.84​
Description 34 of 4

I would assume the VBA would be best suited to handle this, but I have no grasp of it to be able to come close to what I want it to accomplish.

Column references would never change with the data, though it could be created as a table if that's easier.

Any help would be appreciated.

Cheers,
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Welcome to the Board!

Here is one way:
VBA Code: 
Sub MyCopyMacro()

    Dim lr As Long
    Dim r As Long
    Dim rc As Long
    Dim r2 As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all rows of data backwards up to row 2
    For r = lr To 2 Step -1
'       Determine how many rows to copy
        rc = Cells(r, "J")
'       Insert blank rows under current row
        Rows(r + 1 & ":" & r + rc - 1).Insert
'       Copy rows down
        Rows(r).Copy Rows(r + 1 & ":" & r + rc - 1)
'       Populate column J
        For r2 = 0 To rc - 1
            Cells(r + r2, "J") = r2 + 1 & " of " & rc
        Next r2
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Hi Joe,

Thanks for the welcome and the response to the question.

On a scale that almost perfectly what i am looking for, A couple of things though.
when I changed the data to reflect what I will actually be using it created multiple spaces between lines (example below edited to protect data)

It also seem to over ride what is already in the tab instead of creating a new Sheet to dump the data in? I can live with it over writing the data that was there but the spaces are a bit of a pain.

MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
1 of 1
Example 1
6200​
620​
10​
PC
10​
KG
0.2​
Random 1
1​
Example 2
6200​
620​
10​
PC
11​
KG
1.2​
Random 21 of 5
Example 2
6200​
620​
10​
PC
11​
KG
1.2​
Random 22 of 5
Example 2
6200​
620​
10​
PC
11​
KG
1.2​
Random 23 of 5
Example 2
6200​
620​
10​
PC
11​
KG
1.2​
Random 24 of 5
Example 2
6200​
620​
10​
PC
11​
KG
1.2​
Random 25 of 5
Example 3
6200​
620​
10​
PC
12​
KG
2.2​
Random 31 of 2
Example 3
6200​
620​
10​
PC
12​
KG
2.2​
Random 32 of 2
Example 4
6200​
620​
10​
PC
13​
KG
3.2​
Random 41 of 2
Example 4
6200​
620​
10​
PC
13​
KG
3.2​
Random 42 of 2
1 of 1
Example 5
6200​
620​
10​
PC
14​
KG
4.2​
Random 5
1​
1 of 1
Example 6
6200​
620​
10​
PC
15​
KG
5.2​
Random 6
1​
1 of 1
Example 7
6200​
620​
10​
PC
16​
KG
6.2​
Random 7
1​

Thanks,
 
Upvote 0
If you want it to do a copy, simply add some code at the top that copies your data to another sheet, and do it there.

As for the spacing, I did not have that issue. It seemed to work perfectly on the sample data you provided.
Can you provide a data sample in which it did NOT work properly? I need to see what that data looks like BEFORE the macro was run on it.
 
Upvote 0
This is not elegant - but no macros
MrExcelPlayground4.xlsx
ABCDEFGHIJKLM
1MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
2Material 162006202PC2KG18Description 15
3Material 266006308PC8KG14.4Description 28
4Material 362006408PC8KG1.84Description 33
5
6
7
8helper columnsMaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
9111Material 162006202PC2KG18Description 11 of 5
10212Material 162006202PC2KG18Description 12 of 5
11313Material 162006202PC2KG18Description 13 of 5
12414Material 162006202PC2KG18Description 14 of 5
13515Material 162006202PC2KG18Description 15 of 5
14621Material 266006308PC8KG14.4Description 21 of 8
15722Material 266006308PC8KG14.4Description 22 of 8
16823Material 266006308PC8KG14.4Description 23 of 8
17924Material 266006308PC8KG14.4Description 24 of 8
181025Material 266006308PC8KG14.4Description 25 of 8
191126Material 266006308PC8KG14.4Description 26 of 8
201227Material 266006308PC8KG14.4Description 27 of 8
211328Material 266006308PC8KG14.4Description 28 of 8
221431Material 362006408PC8KG1.84Description 31 of 3
231532Material 362006408PC8KG1.84Description 32 of 3
241633Material 362006408PC8KG1.84Description 33 of 3
25            
26            
27            
28            
29            
30            
31            
32            
33            
34            
Sheet29
Cell Formulas
RangeFormula
A9:A24A9=SEQUENCE(SUM(J2:J4))
B9B9=(SUMPRODUCT(--(SUBTOTAL(9,OFFSET($J$2:$J$4,,,ROW($J$2:$J$4)-ROW(INDEX($J$2:$J$4,1))+1))<A9))+1)
C9C9=B9
D9D9=INDEX($A$2:$J$4,$B9,1)
E9E9=INDEX($A$2:$J$4,$B9,2)
F9F9=INDEX($A$2:$J$4,$B9,3)
G9G9=INDEX($A$2:$J$4,$B9,4)
H9H9=INDEX($A$2:$J$4,$B9,5)
I9I9=INDEX($A$2:$J$4,$B9,6)
J9J9=INDEX($A$2:$J$4,$B9,7)
K9K9=INDEX($A$2:$J$4,$B9,8)
L9L9=INDEX($A$2:$J$4,$B9,9)
M9M9=C9&" of "&INDEX($A$2:$J$4,$B9,10)
B10:B34B10=IF(A10<>"",(SUMPRODUCT(--(SUBTOTAL(9,OFFSET($J$2:$J$4,,,ROW($J$2:$J$4)-ROW(INDEX($J$2:$J$4,1))+1))<A10))+1),"")
C10:C34C10=IF(A10<>"",IF(B10=B9,C9+1,1),"")
D10:D34D10=IF(A10<>"",INDEX($A$2:$J$4,$B10,1),"")
E10:E34E10=IF(A10<>"",INDEX($A$2:$J$4,$B10,2),"")
F10:F34F10=IF(A10<>"",INDEX($A$2:$J$4,$B10,3),"")
G10:G34G10=IF(A10<>"",INDEX($A$2:$J$4,$B10,4),"")
H10:H34H10=IF(A10<>"",INDEX($A$2:$J$4,$B10,5),"")
I10:I34I10=IF(A10<>"",INDEX($A$2:$J$4,$B10,6),"")
J10:J34J10=IF(A10<>"",INDEX($A$2:$J$4,$B10,7),"")
K10:K34K10=IF(A10<>"",INDEX($A$2:$J$4,$B10,8),"")
L10:L34L10=IF(A10<>"",INDEX($A$2:$J$4,$B10,9),"")
M10:M34M10=IF(A10<>"",C10&" of "&INDEX($A$2:$J$4,$B10,10),"")
Dynamic array formulas.
 
Last edited:
Upvote 0
Im guessing that a copy and paste did it, I have just tried again and it seems to be fine! i will have a play with the code and get it to copy and past to a new sheet.

Thankyou for the assistance
 
Upvote 0
JamesCanale said:
This is not elegant - but no macros
Click to expand...
Hi James,

I though about using formulas, But the example was trimmed down, I can potentially have hundreds of lines of data, And that many lines with that much code would no be susitainable.

Thanks for the idea though.
 
Upvote 0
Joe4 said:
If you want it to do a copy, simply add some code at the top that copies your data to another sheet, and do it there.

As for the spacing, I did not have that issue. It seemed to work perfectly on the sample data you provided.
Can you provide a data sample in which it did NOT work properly? I need to see what that data looks like BEFORE the macro was run on it.
Click to expand...
Hi Joe,

Following this, i actually just ran a few tests, Including with the sample data i provided, it seems to be restricted to when I am using a Qty of 1

MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
Material 162006202PC2KG18Description 11
Material 262006208PC8KG14.4Description 21
Material 362006208PC8KG1.84Description 31

returns the following

MaterialPlantSlocDeliv.QtyUnPicked QtyUn2Gross WeightDescriptionLabels Required
1 of 1
Material 162006202PC2KG18Description 11
1 of 1
Material 262006208PC8KG14.4Description 21
1 of 1
Material 362006208PC8KG1.84Description 31

Regards

Barrie
 
Upvote 0
OK, your sample did not have any with a "1" in it, so I did not account for that possibility.
Just to need to add a simple "IF" to account for that:
VBA Code: 
Sub MyCopyMacro()

    Dim lr As Long
    Dim r As Long
    Dim rc As Long
    Dim r2 As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all rows of data backwards up to row 2
    For r = lr To 2 Step -1
'       Determine how many rows to copy
        rc = Cells(r, "J")
'       See if need to insert any rows
        If rc = 1 Then
            Cells(r, "J") = "1 of 1"
        Else
'           Insert blank rows under current row
            Rows(r + 1 & ":" & r + rc - 1).Insert
'           Copy rows down
            Rows(r).Copy Rows(r + 1 & ":" & r + rc - 1)
'           Populate column J
            For r2 = 0 To rc - 1
                Cells(r + r2, "J") = r2 + 1 & " of " & rc
            Next r2
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
Sorry, I didn't actually think of the possibility until I tested the script.

That said, it works like an absolute charm! Thanks for all the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Generate a table from a "nested list" (do not use VBA)
Replies
3
Views
160
Fluff
Fluff
A
  • Question
Sum with multiple conditions
Replies
0
Views
134
aarti_rto
A
A
  • Question
sumproduct , data sum
Replies
0
Views
194
aarti_rto
A
A
  • Solved
Need to Filter, Match and Offset
Replies
3
Views
203
Fluff
Fluff
S
  • Solved
Simple nesting issue
Replies
3
Views
287
Fluff
Fluff

Forum statistics

Threads
1,215,471
Messages
6,125,000
Members
449,202
Latest member
Pertotal

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