[RESOLVED] Help with Transpose

[MartinLiss]

I have an array called "Result" that contains a variable number of columns and 252 rows. Assuming there are 2 columns I'd like to use Transpose to place the array into column CJ where the array columns are are concatenated. For example if Result(0,0) is 66 and Result(1,0) = 52 then CJ1 should be 6652. The concatenation of Result(0,1) is 66 and Result(1,1) should follow that, etc.

[Zvoni]

What about 3 (or more) columns?

Result(0,0)=66
Result(1,0)=52
Result(2,0)=18

should then be "665218" in CJ1?

[MartinLiss]

Yes.

[Zvoni]

Questions:
1) are we talking VBA or Excel functions?
2) the values in that array are all longs?
3) 1st dimension is rows, 2nd columns?
4) any ready to use bogus sample-data?

[MartinLiss]

I need VBA.

The array looks like this (truncated).


The values in the array are the result of generating all the combinations of several numbers like 66 52 59 17 15 70 38 30 24 16 14 taken 2 at a time. In some situations I need to take them 3, 4, 5 etc at a time.

It also would be very useful later on to be able to calculate the "54" value.

[Zvoni]

Quick and dirty

Sub main()
'4 Rows, 10 Columns
Dim Result(0 To 3, 0 To 9) As Long
Dim t() As String
Dim i As Long
Dim j As Long
Dim s As String
    'Init some Bogus-Data
    For i = 0 To 9
        Result(0, i) = 66
        'Random Integers between 10 and 99
        Result(1, i) = Int(10 + Rnd * 90)
        Result(2, i) = Int(10 + Rnd * 90)
        Result(3, i) = Int(10 + Rnd * 90)
    Next
    ReDim t(0 To UBound(Result, 1)) 'Get Ubound of Rows
    For j = 0 To UBound(Result, 2)  'Get UBound of Columns
        For i = 0 To UBound(t)
            t(i) = CStr(Result(i, j))
        Next
        s = Join(t, "")             'Assign "s" to your target-cell
        Debug.Print s
    Next
End Sub

[MartinLiss]

Thanks.