+ Reply to Thread
Results 1 to 9 of 9

Transposing data from rows into columns (columns have been merged)

  1. #1
    Registered User
    Join Date
    11-02-2011
    Location
    North, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    6

    Transposing data from rows into columns (columns have been merged)

    Hey there, I am new to the forum and a proficient user of Excel, but by no means a professional. My issue is this...I am trying to transpose data that is in rows into columns. However, the one hitch is that my columns have been merged. I have created a couple hundred raw templates already and didn't think it would be an issue to move that data from horizontal to vertical, but it puts the information in separate cells instead of the large merged cell only. Is there a simple fix to this problem? Any suggestions would be appreciated, would save me a TON of time if I could find a solution to the problem. Thanks in advance if you help me out!

    Best,
    -Cat

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,877

    Re: Transposing data from rows into columns (columns have been merged)

    Hi TommyGato and welcome to the forum,

    If you can attach a sample worksheet we can understand the problem. To attach a sample, click on the "Go
    Advanced" below the message area and then on the Paper Clip Icon above the advanced message area.

    I'd like to see what you mean by:
    my columns have been merged.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    11-02-2011
    Location
    North, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    6

    Smile Re: Transposing data from rows into columns (columns have been merged)

    See if this works...and thanks for the quick response by the way!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-02-2011
    Location
    Maidstone, England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Transposing data from rows into columns (columns have been merged)

    Select the animal names> copy
    Select destination cells> right click> paste special
    tick the boxes "value and numbers formats" and "transpose"> ok

    This works for me and I use it fairly often, however there are limitations to using this, for instance - you have to make sure the number of columns you copy equals the number of destination rows you select.
    Last edited by Krys; 11-02-2011 at 12:33 PM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,877

    Re: Transposing data from rows into columns (columns have been merged)

    Hi TommyGato,

    Merged cells are poop. In general you should never use them. That said..
    Why don't you use the Concatinate function and simply concatinate the cells to the left to fill your merged cell?

    In I1 put the formula
    Please Login or Register  to view this content.
    Now that I read Krys answer above - I think he nailed it. I'm off in lala land thinking something else. Merged cells are still poop and you should copy and paste using Transpose.
    Last edited by MarvinP; 11-02-2011 at 12:31 PM.

  6. #6
    Registered User
    Join Date
    11-02-2011
    Location
    North, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    6

    Talking Re: Transposing data from rows into columns (columns have been merged)

    Thank you both! I used the first response because it seemed much simpler. Still really impressed with the "know how" of both you guys. Really appreciate it and glad I joined this forum. Thanks a ton.

    Best,
    -Cat

  7. #7
    Registered User
    Join Date
    11-02-2011
    Location
    North, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Transposing data from rows into columns (columns have been merged)

    Okay so my problem isn't entirely solved. Turns out that my issue has to do with the information that I am copying. I am copying information from a table that I did not create, it's a rather large Excel document. I counted the number of entries and made sure that it was the same as the number of cells I was trying to paste to. However, it won't let me do the typical "Paste Special". It's asking me to paste it as a picture, bitmap, etc. etc. How can I get this to not happen? I'm assuming I need the cells I am copying to be in a particular format. No idea.

    -Cat

  8. #8
    Registered User
    Join Date
    11-02-2011
    Location
    North, New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Transposing data from rows into columns (columns have been merged)

    Figure it out, had to have the two separate files open in the same Excel program. I had them separate on two different monitors. Woops.

  9. #9
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Transposing data from rows into columns (columns have been merged)

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1