Transpose Large Column to Row

Discussion in 'Microsoft Excel Misc' started by Doan Winkel, Mar 21, 2010.

  1. Doan Winkel

    Doan Winkel Guest

    Hi -

    I have a column with about 25,000+ rows in Excel 2007, and need to somehow
    get this transposed into a row (so with 25,000+ columns). I have tried to do
    this with the TRANSPOSE function using Ctrl-C and using the Paste drop-down
    menu and selecting the Transpose option. But I get an error message that the
    copy area and paste area are not the same size. I presume this is because I
    cannot paste across 25,000+ columns?

    Basically, in the end, what I want to have is a list of each cell with a
    semicolon after each but am just not sure how to do this. Any suggestions
    would be greatly appreciated.

    Thanks
    Doan
     
    Doan Winkel, Mar 21, 2010
    #1
    1. Advertisements

  2. Hello

    Have you tried with Concatenate formula??

    lets pretend that you have all your values in colum A
    from A1 to A[N]
    so on B1 use this fomula

    =A1
    and on b2 this one
    =CONCATENATE(B1,";",A2)
    and copy the formula until B[N]

    so you have an output like this
    A B
    1 a a
    2 b a;b
    3 c a;b;c
    4 d a;b;c;d
    5 e a;b;c;d;e
    6 f a;b;c;d;e;f


    Hope it works

    Regards

    elMedex
    "Doan Winkel" wrote:

    > Hi -
    >
    > I have a column with about 25,000+ rows in Excel 2007, and need to somehow
    > get this transposed into a row (so with 25,000+ columns). I have tried to do
    > this with the TRANSPOSE function using Ctrl-C and using the Paste drop-down
    > menu and selecting the Transpose option. But I get an error message that the
    > copy area and paste area are not the same size. I presume this is because I
    > cannot paste across 25,000+ columns?
    >
    > Basically, in the end, what I want to have is a list of each cell with a
    > semicolon after each but am just not sure how to do this. Any suggestions
    > would be greatly appreciated.
    >
    > Thanks
    > Doan
     
    Alejandro Medinilla elMedex, Mar 21, 2010
    #2
    1. Advertisements

  3. Doan Winkel

    Roger Govier Guest

    Hi Doan

    XL2007 has a limit of 16,384 columns so you cannot do that.

    You could select 16,000 rows, copy>Patse Special>Transpose and then
    select the other 9,000 rows and do the same thing on a different row.
    --
    Regards
    Roger Govier

    Doan Winkel wrote:
    > Hi -
    >
    > I have a column with about 25,000+ rows in Excel 2007, and need to somehow
    > get this transposed into a row (so with 25,000+ columns). I have tried to do
    > this with the TRANSPOSE function using Ctrl-C and using the Paste drop-down
    > menu and selecting the Transpose option. But I get an error message that the
    > copy area and paste area are not the same size. I presume this is because I
    > cannot paste across 25,000+ columns?
    >
    > Basically, in the end, what I want to have is a list of each cell with a
    > semicolon after each but am just not sure how to do this. Any suggestions
    > would be greatly appreciated.
    >
    > Thanks
    > Doan
     
    Roger Govier, Mar 21, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Transpose Column With Gaps to Row With No Gaps?

    Guest, Jun 17, 2005, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    263
    Guest
    Jun 18, 2005
  2. Guest

    transpose column to a row?

    Guest, Jan 13, 2006, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    127
    Guest
    Jan 13, 2006
  3. ali
    Replies:
    6
    Views:
    305
    Don Guillett
    Jul 21, 2007
  4. Maxim
    Replies:
    3
    Views:
    2,605
    Ron Rosenfeld
    Mar 6, 2009
  5. S2

    Transpose large column

    S2, Apr 9, 2009, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    146
Loading...

Share This Page