Excel Transposing Tables

 How to Transpose Table A1: J5 to Table A6: E15?



Best Answer -

1) Select the range A1:J5

2) Right-click and select Copy

3) Right-click on A6 and select Paste Special

4) In the Paste Special window select Transpose (bottom right) and press OK

3 days ago Report Abuse 0 Rating: Good Answer 0 Rating: Bad Answer

Asker's Rating: Asker's Comment: Simple and solve the problem.






Without knowing exactly how you wish to 'transpose' the range A1:J5 into A6:E15, here is one method using a BeforeDoubleClick event handler. It is assumed that you wish to 'move' the data, so after the 'transpose', range A1:J5 will be cleared.



Copy the following code to the clipboard (highlight the entire code, right click inside the code, and 'Copy').



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, j, k

For i = 6 To 15

For j = 1 To 5

For k = 1 To 10

Cells(i, j).Value = Cells(j, k).Value

Next k

Next j

Next i

Range("A1:J5").ClearContents

Target.Offset(0, 1).Select

End Sub



Select the worksheet containing the data you wish to transpose and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right.



Close the VBE.



Double click any cell to transpose the data.



Here is the manner in which the data will be 'moved'. Assume you have the numbers 1-5 in range A1:J5, arranged with the 1's in row one, the 2's in row two, the 3's in row three, etc.



After the event handler runs, the 1's will be in column A, the 2's in column B, the 3's in column C, etc. If this is not the manner in which you wish the data to read, please advise.