Copy value N times in Excel

Question

I have simple list:

  A     B
item1   3
item2   2
item3   4
item4   1

Need to output:

  A
item1
item1
item1
item2
item2
item3
item3
item3
item3
item4

Solution 1

Here is one way of doing it without VBA:

1. Insert a column to the left of A, so your current A and B columns are now B and C.
2. Put 1 in A1
3. Put =A1+C1 in A2 and copy down to A5
4. Put an empty string in B5, by just entering a single quote (') in the cell
5. Put a 1 in E1, a 2 in E2, and copy down as to get 1, 2, ..., 10
6. Put =VLOOKUP(E1,$A$1:$B$5,2) in F1 and copy down.

It should look like this:

| A  | B     | C | D | E  | F     |
|----|-------|---|---|----|-------|
| 1  | item1 | 3 |   | 1  | item1 |
| 4  | item2 | 2 |   | 2  | item1 |
| 6  | item3 | 4 |   | 3  | item1 |
| 10 | item4 | 1 |   | 4  | item2 |
| 11 |       |   |   | 5  | item2 |
|    |       |   |   | 6  | item3 |
|    |       |   |   | 7  | item3 |
|    |       |   |   | 8  | item3 |
|    |       |   |   | 9  | item3 |
|    |       |   |   | 10 | item4 |

Solution 2

Here's the VBA solution. I don't quite understand the comment that VBA won't be dynamic. It's as dynamic as you make it, just like a formula. Note that this macro will erase all data on Sheet1 and replace it with the new output. If you want the desired output on a different sheet, then change the reference to Sheet2 or what have you.

Option Explicit

Sub MultiCopy()

Dim arr As Variant
Dim r As Range
Dim i As Long
Dim currRow As Long
Dim nCopy As Long
Dim item As String

'store cell values in array
arr = Sheet1.UsedRange
currRow = 2

'remove all values
Sheet1.Cells.ClearContents
Sheet1.Range("A1") = "A"

For i = 2 To UBound(arr, 1)
    item = arr(i, 1)
    nCopy = arr(i, 2) - 1
    If nCopy > -1 Then
        Sheet1.Range("A" & currRow & ":A" & (currRow + nCopy)).Value = item
        currRow = currRow + nCopy + 1
    End If
Next

End Sub