Invert a frequency table?

[Chuck W.]

Hi,

Can anyone tell me how to "invert" a frequency table? That is, I have
some data organized into two columns: "Frequency Count" and "Number"
like so:

4 1
3 2
2 3

and I'd like to convert it to a single column like this:

1
1
1
1
2
2
2
3
3

The reason I'm trying to do this is because I would like to use a
function such as STDEV.S(), but I need the "expanded" version of the
data...

Thanks very much for any help you can provide.

 

[Rick Rothstein]

Can anyone tell me how to "invert" a frequency table? That is, I have

some data organized into two columns: "Frequency Count" and "Number"
like so:

4 1
3 2
2 3

and I'd like to convert it to a single column like this:

1
1
1
1
2
2
2
3
3

Here is a macro that will do what you asked for...

Sub ExpandTable()
Dim X As Long, LastRow As Long
Const StartRow As Long = 5
Const StartCol As Long = 3
LastRow = Cells(Rows.Count, StartCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow Step -1
Rows(X + 1).Resize(Cells(X, StartCol).Value - 1).Insert
Cells(X, StartCol).Resize(Cells(X, StartCol).Value). _
Value = Cells(X, StartCol + 1).Value
Next
Columns(StartCol + 1).Clear
Application.ScreenUpdating = True
End Sub

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

*** Correction ***

Sorry, I left some test values in the constant (Const) statements without
mention them. I'm assuming your table starts in A1, so the code should have
looked like this....

Sub ExpandTable()
Dim X As Long, LastRow As Long
Const StartRow As Long = 1
Const StartCol As Long = 1
LastRow = Cells(Rows.Count, StartCol).End(xlUp).Row
Application.ScreenUpdating = False
For X = LastRow To StartRow Step -1
Rows(X + 1).Resize(Cells(X, StartCol).Value - 1).Insert
Cells(X, StartCol).Resize(Cells(X, StartCol).Value). _
Value = Cells(X, StartCol + 1).Value
Next
Columns(StartCol + 1).Clear
Application.ScreenUpdating = True
End Sub

Obvious (now) is the the fact that you should assign your actual starting
row and column numbers to the appropriate constant above.

Rick Rothstein (MVP - Excel)