Excel question: auto populate rows based on criteria

Question

I have a simple table with 3 rows and 3 columns:

Column 1 - Order #, Column 2 - Part #, Column 3 - Quantity

Row 1: 123 | ABC | 5

Row 2: 456 | XYZ | 7

Row 3: 789 | OPQ | 2

How can I set up either a formula (preferably) or VBA to automatically populate, in a separate sheet:

5 rows of 123 | ABC, followed by

7 rows of 456 | XYZ, followed by

2 rows of 789 | OPQ

Thanks in advance,

Answer 1

Below is a small VBA procedure that gets all of the data from the first worksheet and puts it into an array. It then loops this array, performing a loop on the last element to create the required number of rows for each Order/Part:

Sub sExpandData()
    Dim wsIn As Worksheet
    Dim wsOut As Worksheet
    Dim lngLastRow As Long
    Dim aData() As Variant
    Dim lngLoop1 As Long
    Dim lngLoop2 As Long
    Dim lngRow As Long
    Set wsIn = ThisWorkbook.Worksheets("Data")
    Set wsOut = ThisWorkbook.Worksheets("Expanded")
    lngLastRow = wsIn.Cells(wsIn.Rows.Count, "A").End(xlUp).Row
    aData = wsIn.Range("A1:C" & lngLastRow)
    lngRow = 1
    For lngLoop1 = LBound(aData, 1) To UBound(aData, 1)
        For lngLoop2 = 1 To aData(lngLoop1, 3)
            wsOut.Cells(lngRow, 1) = aData(lngLoop1, 1)
            wsOut.Cells(lngRow, 2) = aData(lngLoop1, 2)
            lngRow = lngRow + 1
        Next lngLoop2
    Next lngLoop1
    Set wsIn = Nothing
    Set wsOut = Nothing
End Sub

Regards,