Results 1 to 9 of 9
  1. #1
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    If i have a 2-dimensional array, is there a function that would convert the array to a single column or row?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is your 2D array just one column (or row) or multiple?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by rory View Post
    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  5. #5
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by SteveA View Post
    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  7. #7
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    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?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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]

  9. #9
    5 Star Lounger ibe98765's Avatar
    Join Date
    Aug 2001
    Location
    Bay Area, California, USA
    Posts
    966
    Thanks
    19
    Thanked 4 Times in 4 Posts
    OK, that works great.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •