duplicating rows by quantity

Question

i would like to duplicate the rows by the quantity identified in the columns. i am having problems with this one

so far i have been able to duplicate the rows by quantity but haven't been able to add which 'Block' that row is for

this is the data: enter image description here

expected result: enter image description here

this is the code i am using. it's an adjusted version of another code

Sub CopyBlocks() Dim StartRow, LastRow, NewSheetRow As Long Dim n, i As Integer

Worksheets("test").Activate
LastRow = Cells(Rows.Count, 7).Row
NewSheetRow = 10

For StartRow = 10 To LastRow
n = CInt(Worksheets("test").Range("AA" & StartRow).Value)
For i = 1 To n
    Worksheets("test2").Range("C" & NewSheetRow).Value = Worksheets("test").Range("g" & StartRow).Value
    Worksheets("test2").Range("D" & NewSheetRow).Value = Worksheets("test").Range("H" & StartRow).Value
    Worksheets("test2").Range("E" & NewSheetRow).Value = Worksheets("test").Range("I" & StartRow).Value
    Worksheets("test2").Range("F" & NewSheetRow).Value = Worksheets("test").Range("J" & StartRow).Value
    Worksheets("test2").Range("G" & NewSheetRow).Value = Worksheets("test").Range("K" & StartRow).Value

    NewSheetRow = NewSheetRow + 1
Next i
Next StartRow

End Sub

Can you please edit your question to provide the code you have so far? — cybernetic.nomad
would you ever have a number greater than 1 in your crosstab? — Ryan B.
In SQL this would be an UNPIVOT. Use that as your VB search reference — KeithL
1st search. excel-university.com/unpivot-excel-data Apaprently you can do this in just a GUI without VBA — KeithL
@RyanB. yes the numbers could be any value. but most likely under 20 — barabont

Ryan B. Ryan B. · Accepted Answer · 2019-04-08T19:42:47

If you are using excel 2016, then you can use PowerQuery to unpivot this data set very nicely. Keith has a very useful link for you in the comments. Filter away the zeros and you're almost at your solution. A little complexity comes into the game with the possibility of those duplicate rows. The List.Numbers function can get you there if you're interested in getting into the guts of the M language just a little bit.

However, it's also not too hard to crack this out in VBA. I'd suggest a little different strategy which is just to iterate over the range of your crosstab, plucking out the row and column headers when you hit a count more than 0.

Sub foo()

    Dim outputRow As Integer

    'start your output at whatever row is best
    outputRow = 1

    'set your range to cover the counts in your crosstab
    For Each c In Range("A1:Z99")
        If c.Value > 0 Then

            For i = 1 To c.Value

                    'write the values off the current row headers over to comparable positions in your output row
                    Worksheets("test2").Cells(outputRow, 3).Value = Cells(c.Row, 1).Value
                    Worksheets("test2").Cells(outputRow, 4).Value = Cells(c.Row, 2).Value
                    .
                    .
                    .

                    'write the values off the current column headers into output row
                    Worksheets("test2").Cells(outputRow, 8).Value = Cells(1, c.Column).Value

                outputRow = outputRow + 1
            Next i

        End If
    Next c
End Sub

Good luck, hope it helps

duplicating rows by quantity

1 Answers