Transpose from right to left

E

eqbal

Board Regular
Joined
Sep 6, 2002
Messages
92
hi

I have 19000 rows of data spreading from column A to V. I need to transpose these in a from left to right! That is, i need column A to sit instead of column V and all the columns in between in the same way.

please comment.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
eqbal said:
I have 19000 rows of data spreading from column A to V. I need to transpose these in a from left to right! That is, I need column A to sit instead of column V and all the columns in between in the same way.
Click to expand...
Does this macro do what you want...
Code: 
Sub RearrangeColumns()
  Dim X As Long, LastRow As Long, Letters As Variant, NewLetters As Variant
  Const NewOrder As String = "V,U,T,S,R,Q,P,O,N,M,L,K,J,I,H,G,F,E,D,C,B,A"
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Letters = Split(NewOrder, ",")
  ReDim NewLetters(1 To UBound(Letters) + 1)
  For X = 0 To UBound(Letters)
    NewLetters(X + 1) = Columns(Letters(X)).Column
  Next
  Range("A1").Resize(LastRow, UBound(Letters) + 1) = _
    Application.Index(Cells, Evaluate("ROW(1:" & LastRow & ")"), NewLetters)
End Sub
 
Upvote 0
Assume you are doing it on Sheet2, and your original data is on Sheet1
You may try to input the following formula in A1 of Sheet2
=INDEX(Sheet1!$A$1:$V$19000,ROW(),COLUMNS($A$1:$V$1)-COLUMN()+1)
Copy down and across

Note: Given the large range of data you are talking about, this may NOT be an efficient way. Only suggest you to do so if you need to one-time solution only. Remind you to convert all formula back to value after it's done.

I am sure there is a VBA way to do it nicely. Let's see.



eqbal said:
hi

I have 19000 rows of data spreading from column A to V. I need to transpose these in a from left to right! That is, i need column A to sit instead of column V and all the columns in between in the same way.

please comment.
Click to expand...
 
Upvote 0
both solutions work perfectly. thanks again

in fact the solutions in MrExcel are produced much faster than i can check them all :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Reversing a series in a range,
Replies
8
Views
46
Alex Blakenburg
A
A
  • Question
how to add my signature with images to macro
Replies
1
Views
156
Domenic
D
V
  • Question
Help Needed for a Macro to Transpose
Replies
6
Views
133
alansidman
A
S
  • Solved
Macro to add quotes, commas, transpose and select the transposed rows
2
Replies
14
Views
417
Peter_SSs
Peter_SSs
J
  • Question
Complex Move data from two spreadsheets with similar data in different columns to a specific column in an existing spreadsheet(Sheet 3)
Replies
0
Views
96
JMC57
J

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top