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

**TommyGato** · 11-02-2011, 12:00 PM

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

**MarvinP** · 11-02-2011, 12:05 PM

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.

**TommyGato** · 11-02-2011, 12:16 PM

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

**Krys** · 11-02-2011, 12:28 PM

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.

**MarvinP** · 11-02-2011, 12:28 PM

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.

**TommyGato** · 11-02-2011, 12:55 PM

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

**TommyGato** · 11-02-2011, 01:17 PM

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

**TommyGato** · 11-02-2011, 01:44 PM

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.

**ConneXionLost** · 11-02-2011, 02:45 PM

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