how to convert an array

N

newengland

Hi

I have an array of values as follows

3 25
1 10
4 12
6 7

the first column represents the number of instances of the row.
would like to convert this as follows:

25
25
25
10
12
12
12
12
7
etc.

how can I do this formulaically? or formulaically with a UDF?

thank
 
B

bplumhoff

Hello,

A suggestion for a UDF:
Option Explicit

Function one_dim_array(rI As Range) As Variant
'Converts an argument array of frequencies and values into
'a one-dimensional array.
'Example:
'3 25
'1 10
'4 12
'6 7
'will become 25 25 25 10 12 12 12 12 7 7 7 7 7 7.
'Remember to select sufficient count of cells for result
'and to enter function as array-formula (CTRL + SHIFT + ENTER).
Dim vR As Variant
Dim r As Range
Dim b As Boolean
Dim lF As Long, i As Long, j As Long

With Application.Caller

ReDim vR(1 To .Rows.Count, _
1 To .Columns.Count)
i = 1
j = 1
b = True
For Each r In rI
If b Then
lF = r.Value
Else
Do While lF > 0
vR(i, j) = r.Value
j = j + 1
If j > .Columns.Count Then
j = 1
i = i + 1
End If
lF = lF - 1
Loop
End If
b = Not b
Next r

End With

one_dim_array = vR

End Function

HTH,
Bernd
 
Top