Excel Formula to Create Rows Based on qty

Closed
-
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
-
Hello,

I have an Excel 2010 worksheet with the following data

Name ID Qty
Joe 123 15
Bob 456 8
Ed 234 0
Smith 345 1

Basically each row is a new person with his ID and the Qty is the number of times that I need to copy the Name and ID for individual rows (so in the case of Joe, I would have 15 rows with his name and ID) If the Qty is 0, then that line needs to be deleted. I have tried numerous times to write formulas, but can't get anything to work. How do I fix this?

I have also copied and pasted several times different VBA codes I found online, with no luck. I am not familiar with VBA, so I may be doing something wrong.

Any help would be greatly appreciated!

1 reply

Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Use the Following code it will helps you,
Before that make a copy of your file, because after running the macro you are not able to undo the operation.

Sub Macro1()
Dim i, j, k, l As Long
Dim lastrow As Long

Sheets(2).Select
Rows.Delete
Sheets(1).Select
lastrow = Range("A" & Rows.Count).End(xlUp).Row
If lastrow < 2 Then Exit Sub
Rows(1).Select
Selection.Copy
Sheets(2).Select
Rows(1).Select
ActiveSheet.Paste
For I = 2 To lastrow
    Sheets(1).Select
    Range("A" & i, "B" & i).Copy
    j = Range("C" & i).Value
    If j = "" Then Exit Sub
    If j <> 0 Then
        Sheets(2).Select
        k = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
        Range("a" & k).Select
        ActiveSheet.Paste
        If j > 1 Then
        Range("A" & k, "B" & k + j - 1).Select
        Selection.FillDown
        End If
    Else
     Rows(i).Delete
     I = I - 1
     End If
Next i
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True

End Sub


Thanks,
Rahul. :)