Announcement

Collapse
No announcement yet.

Transpose Five Column Array To A Single Column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    .
    .
    .

  • #2
    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:

    Code:
    =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.

    Comment


    • #3
      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
      Kris

      ExcelFox

      Comment

      Working...
      X