Array to row/column?

[ibe98765]

If i have a 2-dimensional array, is there a function that would convert the array to a single column or row?

[rory]

Is your 2D array just one column (or row) or multiple?

[ibe98765]

Is your 2D array just one column (or row) or multiple?

I though a single row/column was a vector and multiple rows/columns was an array?

My example might look like this (multiple row/columns):

1 2 3 4 5 6
6 5 4 3 2 1
3 2 1 6 5 4

etc.

[sdckapr]

Add to cell in row 1:
=INDEX($A$1:$F$3,MOD(ROW()-1,ROWS($A$1:$F$3))+1,INT((ROW()-1)/ROWS($A$1:$F$3))+1)

and copy it down to go down col1, then 2 then 3 taking each row in turn

or

Add to cell in row 1:
=INDEX($A$1:$F$3,INT((ROW()-1)/COLUMNS($A$1:$F$3))+1,MOD(ROW()-1,COLUMNS($A$1:$F$3))+1)

and copy it down to go across row 1, then row2 then row3 taking each column in turn

Change the range as desired in all locations...

Steve

[ibe98765]

Add to cell in row 1:
=INDEX($A$1:$F$3,MOD(ROW()-1,ROWS($A$1:$F$3))+1,INT((ROW()-1)/ROWS($A$1:$F$3))+1)

and copy it down to go down col1, then 2 then 3 taking each row in turn

or

Add to cell in row 1:
=INDEX($A$1:$F$3,INT((ROW()-1)/COLUMNS($A$1:$F$3))+1,MOD(ROW()-1,COLUMNS($A$1:$F$3))+1)

and copy it down to go across row 1, then row2 then row3 taking each column in turn

Change the range as desired in all locations...

Steve

I don't think this is what will work. It sounds like I would have to repeat this involved process for each new array.

Maybe I need a macro of some sort?

[sdckapr]

It is not that involved to copy the formula and change the range...

But a macro is possible. In fact there are 4 possibilities which do you want? The 2D array into a row or into a column? Do you want to read the values from the 2D array by columns or by rows? [ie The first element is row1, col1. Which is the 2nd element: row1 col2 or row2 col1?]

Steve

[ibe98765]

The numbers are not significant in position and can be read in any direction. A column output would be fine.

So there isn't an existing Excel function to do this automatically?

[sdckapr]

You can try this. Select the range of the 2D array and then run the macro. It will place the column of items in the same column as the start of the range starting at 2 rows after the end of the range. This can be modified if desired...

[codebox]Option Explicit
Sub ArrayToColumn()
Dim rng As Range
Dim iCols As Integer
Dim lRows As Long
Dim lRow As Long
Dim iCol As Integer
Dim x As Long

Set rng = Selection
With rng
iCol = .Column
lRow = .Row + .Rows.Count
End With

For x = 1 To rng.Cells.Count
Cells(lRow + x, iCol) = rng.Cells(x)
Next

End Sub
[/codebox]

[ibe98765]

OK, that works great.

Thanks!