unstack / loop through data repeat number by category value

Confidentjohn

Board Regular
Joined
Mar 3, 2009
Messages
73
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top