Align several columns into 1 columns

  • I have 3 columns:
    -------------------
    a D g
    b E h
    c F


    and would like to have them aligned into 1 single column:
    --------------------------------------------------------------
    a
    b
    c
    D
    E
    F
    g
    h


    Any suggestion formulas, functions, etc. for what i could best use for this purpose.
    In reality, i have about 200 columns with different number of rows.
    So i need to align smarter than manually.

  • Re: Align several columns into 1 columns


    Try this:

  • Re: Align several columns into 1 columns


    Another way with formulas.


    A helper column in B with this array-entered formula in B5 and filled down until you get blanks.

    Code
    1. =IF(ROWS($5:5)>COUNTA($A$1:$C$3),"",SMALL(IF($A$1:$C$3<>"",COLUMN($A$1:$C$3)),ROWS($5:5)))

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)
    Then this regular formula in A5 filled down until you get blanks.

    Code
    1. =IF(B5="","",INDEX($A$1:$C$3,COUNTIF($B$5:B5,B5),B5))



    [Table="class: grid"]

    [tr]

    [td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

    A

    [/td][td="bgcolor: #DCE6F1"]

    B

    [/td][td="bgcolor: #DCE6F1"]

    C

    [/td]

    [/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    1

    [/td]

    [td]

    a

    [/td][td]

    D

    [/td][td]

    g

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    2

    [/td]

    [td]

    b

    [/td][td]

    E

    [/td][td]

    h

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    3

    [/td]

    [td]

    c

    [/td][td]

    F

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    4

    [/td]

    [td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    5

    [/td]

    [td]

    a

    [/td][td]

    1

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    6

    [/td]

    [td]

    b

    [/td][td]

    1

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    7

    [/td]

    [td]

    c

    [/td][td]

    1

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    8

    [/td]

    [td]

    D

    [/td][td]

    2

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    9

    [/td]

    [td]

    E

    [/td][td]

    2

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    10

    [/td]

    [td]

    F

    [/td][td]

    2

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    11

    [/td]

    [td]

    g

    [/td][td]

    3

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    12

    [/td]

    [td]

    h

    [/td][td]

    3

    [/td][td]

    [/td][/tr]


    [tr]

    [td="bgcolor: #DCE6F1"]

    13

    [/td]

    [td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [/table]

  • Re: Align several columns into 1 columns


    This would be another formula way without a helper column.

    Code
    1. =IF(ROWS($5:5)>COUNTA($A$1:$C$3),"",INDEX($A$1:$C$3,MOD(ROWS($5:5),-3)+3,CEILING(ROWS($5:5)/3,1)))