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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Months From 1st Purchase[/TD]
[TD]Number of Customers 2nd Order[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Months From 1st Purchase[/TD]
[TD]Number Of Customers 2nd Order[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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