Transpose the data in alternate column

[hputhraya]

Hi,

I need to get the data transposed in an excel worksheet. I have attached an excel sheet with the samle data. Can some one help me with a macro which can transpose the column data into row data?

Your help is much appreciated,

regards
Harsha

[tigertiger]

Hi,
...transpose the column data into row data?

Why don't you use the Paste special function in Edit menu, or follow steps:

- Selection the copied range, then Press Ctrl+C
- Right Click on the cell needed paste and chose Paste special and then tick Transpose in the appeared dialog ->OK

IF you want to make a SUB (code) -> recorded the above steps and recoding for being suitable

------

[hputhraya]

Hi,
I tried using paste special with transpose option, but it is not giving me the output as desired.
I have tried out it in the attachment.

Regards
Harsha

[JBeaucaire]

This macro will do what you've described. I assume there will be many paired rows with data in it, so this will work to reorganize them all. It will also serve if there really is just one.

Please Login or Register  to view this content.

[JBeaucaire]

You've indicated in a private message that there are over 30,000 rows of this data. You're going to run out of room before this macro is finished, there are only 65,536 rows available in Excel 2003.

Note, your current layout is actually superior to the one you are trying to change to...the only change I would recommend is putting all the values in a single row for one item rather than having them spread over multiple columns.

Reformatting to the Column format like you've asked makes a much less usable/reportable/analyzable dataset.

[hputhraya]

Thanks a lot Jerry. You made my work so much easier!!
I'm using excel 2007, which i believe has 1 milllion rows.
As you said, the current layout is superior to analyse, but i have to load it into SQL Server 2005 table which makes it impossible with my original layout.

Well, thanks for all the info!!

Cheers
Harsha

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.