Transposing a large "ladder" shape matrix

Do you run out of memory copying, doing paste special transpose, skip blanks?

then find and select, Got to special, slect blanks and make 100?


Failing this you will need a vba approach to the solution

I am not sure what is an out of memory copying?

When I select all the cells with normal square targeting, paste transpose does not work since it would overlap the original data

Originally Posted by davsth

Do you run out of memory copying, doing paste special transpose, skip blanks?

then find and select, Got to special, slect blanks and make 100?


Failing this you will need a vba approach to the solution

My mistake firstly, make a copy of the original sheet, as excel does not like copy and paste ranges to overlap, unless they are an exact match

copy the data, then go to paste special. you may need to look at the options at the bottom of the box, i do not have your version of excel, but there are 2 tick boxes for transpose and skip blanks slect both

the key statement is selecting the skip blanks box alongside paste special transpose. As the blocks you are transposing are blank in these cells they will not overwrite

As it was a large amount of data, i was wondering if there might be an issue copying and pasting, it depends on your PC configuration

How could this formula apply to the large matrix that I had?

How could I apply this macro to my own original data?

Originally Posted by p45cal

The attached just automates davsth's recommendations.
Select the area to be processed thus:
Attachment 707780
[I don't know why this picture isn't showing, but clicking it will show it]
then click the button.
It runs this macro:

Please Login or Register  to view this content.

Originally Posted by s2031015

How could I apply this macro to my own original data?

Select the existing data as shown in the picture in msg#7, then run the macro. It will add the 'reflected' data to your existing data and you must ensure that there's at least 1 spare row above your selection.