Hi team I have my data as first photo but I want my data column wise like my second photo. At the end of every address there is a blank cell. Transpose is not working for Multiple address.
Let's say that you want the "transposed" range to start in cell E5.
Enter the following formula in that cell:
=INDEX($A:$A,6*(ROW(E5)-ROW($E$5))+COLUMN(E5)-COLUMN($E$5)+5)&""
If you want to start in another cell, change E5 and $E$5 accordingly.
Fill to the next 4 cells to the right, then fill down.
Accepted Solutions
Let's say that you want the "transposed" range to start in cell E5.
Enter the following formula in that cell:
=INDEX($A:$A,6*(ROW(E5)-ROW($E$5))+COLUMN(E5)-COLUMN($E$5)+5)&""
If you want to start in another cell, change E5 and $E$5 accordingly.
Fill to the next 4 cells to the right, then fill down.