Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Transpose Five Column Array To A Single Column

  1. #1
    Join Date
    12th October 2006
    Posts
    9

    Transpose Five Column Array To A Single Column

    A B C D E
    1 2 3 4 5
    6 7 8 9 10
    11 12 13 14 15

    You have a data array of numbers appearing in columns A - E. The numbers appear in the order indicated above. The data array can vary from 1 to 7000 numbers depending on data downloaded to Excel. Therefore, the data will appear from Row 2 to possibly Row 1400. The goal is to place the numbers in order of appearance down a single column.
    For instance if the results were to appear in column G the would appear in the following cell reference order:
    A1
    B1
    C1
    D1
    E1
    A2
    B2
    C2
    D2
    E2
    A3
    B3
    C3
    D3
    E3
    .
    .
    .

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    5th August 2003
    Posts
    531

    Re: Transpose Five Column Array To A Single Column

    I think I have a solution, but it assumes the following:

    1) No data gaps:
    All cells in an upper row or further left column will be filled before any in a lower row or further right column (that is, if there's a value in C3, then there must have been values in A1:E1, B2:E2, and A3:B3)

    2) there will only be 5 columns and up to 1400 rows (this is easier to modify -but the provided formula conforms to these rules).


    Try the following formula in G1:

    VB:
    =If(CELL("row", F1)<=COUNT($A$1:$E$1400), INDEX($A$1:$E$1400,INT((CELL("row", F1)-1)/5)+1,MOD(CELL("row", F1)-1, 5)+1), "") 
    
    
    Copy it down to G7000.

    If assumption 1 isn't true, maybe a macro approach is the way to go. Post back if that's the case.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Transpose Five Column Array To A Single Column

    Hi,

    In G1 and copied down,

    =IF(ROWS($A$1:$A1)<=COUNT($A$1:$E$3),INDEX($A$1:$E$3,INT((ROWS($A$1:$A1)-1)/5)+1,MOD(ROWS($A$1:$A1)-1,5)+1),"")

    HTH

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Transpose Single Column List Into X Columns
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: August 23rd, 2008, 14:50
  2. Transpose Column Range Into Single Cell With Delimiter
    By heizeer in forum Excel General
    Replies: 3
    Last Post: May 15th, 2008, 06:41
  3. Transpose Each Row Of X Columns Into Single Column
    By FRANTIC in forum Excel General
    Replies: 7
    Last Post: April 27th, 2008, 09:07
  4. Lookup formula single column array
    By kollizion in forum Excel General
    Replies: 6
    Last Post: September 8th, 2005, 02:12
  5. transpose multiple row array to a single column
    By laskuh in forum Excel General
    Replies: 9
    Last Post: March 21st, 2005, 03:37

Bookmarks

Posting Permissions

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