unstack / loop through data repeat number by category value

[Confidentjohn]

Hi

I have some data that i need to expand out / unstack in excel with either some VBA or a formula.

The data looks like this, (But is variable so will change, the values and the height data)

Months From 1st Purchase	Number of Customers 2nd Order
0	5
1	4
2	5
3	3
4	1
5	6

<tbody>
</tbody>

I want to unstack the data to so that the Months from purchase number is repeated the number of times it is seen for a customer column 2nd order column. So the table above would look like below.

For example

• 5 Customers placed a second order in the same month as the first order (represented by 0 in months from 1st purchase) so the number 5 would be repeated 5 times.
• 4 customers placed a second order 1 month after their 1st purchase so the number one would be repeated 4 times.
• and so on, (in real data this can go into the 1000's)

Months From 1st Purchase	Number Of Customers 2nd Order
0	0
0	0
0	0
0	0
0	0
1	1
1	1
1	1
1	1
2	2
2	2
2	2
2	2
2	2
3	3
3	3
3	3
4	4
5	5
5	5
5	5
5	5
5	5
5	5

<tbody>
</tbody>

Is there a cleaver macro or formula that can loop through something like this?

Thanks in advance

 

[Akuini]

Try this:

Sub a926740()
Dim i As Long
Dim x As Long
Dim rr As Long
Application.ScreenUpdating = False

rr = Range("B" & Rows.count).End(xlUp).row
For i = rr To 2 Step -1
    x = Cells(i, "B")
    If x = 1 Then
    Range(Cells(i, "A"), Cells(i + x - 1, "B")).Value = Cells(i, "A").Value
    Else
    Rows(i + 1 & ":" & i + x - 1).Insert shift:=xlShiftUp
    Range(Cells(i, "A"), Cells(i + x - 1, "B")).Value = Cells(i, "A").Value
    End If
Next
Application.ScreenUpdating = True

End Sub

 

[Confidentjohn]

Thank you so much, works great