Transpose cells

[pittopitto]

Hello guys!

I have a problem. I hope I am putting the thread in a right group.

I have a worksheet with 100 columns x 350 rows full of data. Because I need to use the data for SPSS, I need to 'transpose' (350 columns x 100 rows) the data and then put each column at the end of the first column. Here is an example on what I would like to do:

1. Copy all the 100 columns X 350 rows
2. Paste Special [Transpose] the copied cells in a new worksheet (It didn't work because Excel can have maximum 250 or so columns, so I am 'splitting' the copied cells in two parts)
3. Cut from the second column to the end
4. Go to the end of the first column and paste.

The steps 3 and 4 are repeated all over until the final column to be cut. I need one looooooong column made of 100x350 data (35000 rows!).

I tried by recording a macro but it didn't work... Do you have any idea? PLEASE it's very important for me....

Attached here is a simple reduced worksheet to 200 rows and 50 columns if you want to experiment a little bit.

Many Thanks

pitto

[Paul]

Hi pittopitto,

I think this code might do what you're looking for:

Please Login or Register  to view this content.

Change "Sheet1" to your new sheet if named something else. This should work for your original data set (100 cols x 350 rows) since it is not actually transposing the data, but cycling through the columns in each row and pasting each cell vertically. Your 50 column x 200 row sheet turned into a 10,000 row list in Sheet1 column A during my tests.

Edit:
PS - On my laptop it took 15 seconds to complete for 50 x 200. I tried for 100 x 350 and it took about 45 seconds. Your mileage may vary.

[pittopitto]

Wow, that's what I wanted... Thank you very much!! On my two years old computer it took about a minute to execute the program...

Many Thanks
pitto