Disaggregating data

[afndst]

Hello

Does anyone know VBA code to transform X into Y?

X	F	Y
1	1	1
2	2	2
3	1	2
		3

<colgroup><col width="64" span="3" style="width: 48pt; text-align: right;"> </colgroup><tbody>
</tbody>

Regards

António Teixeira

 

[Norie]

Try this.

Sub Disaggregate()
Dim arrIn As Variant
Dim arrOut()
Dim I As Long
Dim J As Long
Dim cnt As Long

    arrIn = Range("A2:B4")
    
    ReDim arrOut(1 To Application.Sum(Application.Index(arrIn, , 2)), 1 To 1)
    
    For I = LBound(arrIn) To UBound(arrIn)
    
        For J = 1 To arrIn(I, 2)
            cnt = cnt + 1
            
            arrOut(cnt, 1) = arrIn(I, 1)
        Next J
    
    Next I
    
    Range("C2").Resize(UBound(arrOut)).Value = arrOut
    
End Sub

 

[afndst]

Hello

It works perfectly.

Thank you very much

Try this.

Sub Disaggregate()
Dim arrIn As Variant
Dim arrOut()
Dim I As Long
Dim J As Long
Dim cnt As Long

    arrIn = Range("A2:B4")
    
    ReDim arrOut(1 To Application.Sum(Application.Index(arrIn, , 2)), 1 To 1)
    
    For I = LBound(arrIn) To UBound(arrIn)
    
        For J = 1 To arrIn(I, 2)
            cnt = cnt + 1
            
            arrOut(cnt, 1) = arrIn(I, 1)
        Next J
    
    Next I
    
    Range("C2").Resize(UBound(arrOut)).Value = arrOut
    
End Sub