Duplicate number of lines based on conditions

[piethein123]

I have a question regarding duplicating a number of lines based on condition.

so for example , based on the condition in column F "packed road" , i would need to generate an additional amount of lines with the exact same info. In this case 4 additional lines

Is this possible in excel?

Plant	Ship-To Party	Material	Incoterms	Shipping condition code	Shipping condition (general)	Avg
1​	2​	3​	DAP	10​	PACKED ROAD	0,207450335​

 

[Anthony47]

so for example , based on the condition in column F "packed road" , i would need to generate an additional amount of lines with the exact same info. In this case 4 additional lines
You didn't explain why that record would require 4 additional lines, so my guess is that 4 = Column C + 1 (column C is 3 in your example, so a total of 5 rows will be created on sheet2)
With this guess, this macro will create on Sheet2 a replica of the starting data with the additional lines:

Sub NewList()
Dim I As Long, RArr, LastCol As Long, RNext As Long, LFor As String
Dim RowsNum As Long
'
LFor = "PACKED ROAD"
'
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Sheets("Sheet2").Range("A:A").Resize(, LastCol).ClearContents       'Clear Sheet2!
RNext = 1
For I = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    RArr = Cells(I, 1).Resize(1, LastCol).Value
    If RArr(1, 6) = LFor Then RowsNum = RArr(1, 3) + 2 Else RowsNum = 1
    Sheets("Sheet2").Cells(RNext, 1).Resize(RowsNum, LastCol) = RArr
    RNext = RNext + RowsNum
Next I
End Sub

 

[piethein123]

Sorry for the confusion. The additional amount of rows is not based on the value column c. It is based on how many lines i need to upload which can be different based on information in column D or E or F. I would like the VBA to use the additional amount of rows based on what number i fill in a cell (or even the formula).
Would that be possible ?

 

[Anthony47]

Would that be possible ?

Yes, if you explain where the vba can read the information about the number of rows to add

 

[piethein123]

OK , i can add that in the last column ; so this is the exact layout of the file. I have made up the numbers so that's not important. Column K is the amount of times the specific row needs to be duplicated.

Plant	Ship-To Party	Material	Incoterms	Shipping condition code	Shipping condition (general)	Total EUR	Total gross without pallet	Per unit Gross KG without pallet	avg	lines to added
1​	2​	3​	DAP	10​	PACKED ROAD	10​	20​	0,207450335​	0,12​	4​

 

[Anthony47]

So modify this line in my macro:

If RArr(1, 6) = LFor Then RowsNum = RArr(1, 11)  Else RowsNum = 1

 

[piethein123]

wow , absolutely amazing. Thanks it worked!

 

[Anthony47]

Thank you for the feedback
(maybe it had to be ...Then RowsNum = RArr(1, 11) +1 Else ... ?)