mirriring columns of data

[Matt Knight]

Hi there, hopefully someone can led me a hand:

Basically I have about 63K rows of data and 52 columns. What I need
to to is flip the columns around so that the data in Col BC is in D
Col D and vive versa. The header in the top row (row 5, starting in
Col D) runs from 0-51, so it's basically a matter of sorting on this
row (for less data, I'd transpose, sort and then transpose back-
simple, but I don't have 63k columns!)

If anyone can offer me a suggestion, I'd appreciate it (complete
novice at VBA though, so bear with me).

For reference I tried modifying the macro found here (unsuccessfully)
-
http://groups.google.com/group/micr...84214?lnk=gst&q=sort+by+rows#e3c0d4b271b84214

Cheers
Matt

 

[Don Guillett]

Hard to visualize
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[coj]

Hi there, hopefully someone can led me a hand:

Basically I have about 63K rows of data and 52 columns. What I need
to to is flip the columns around so that the data in Col BC is in D
Col D and vive versa. The header in the top row (row 5, starting in
Col D) runs from 0-51, so it's basically a matter of sorting on this
row (for less data, I'd transpose, sort and then transpose back-
simple, but I don't have 63k columns!)

If anyone can offer me a suggestion, I'd appreciate it (complete
novice at VBA though, so bear with me).

For reference I tried modifying the macro found here (unsuccessfully)
-
http://groups.google.com/group/micr...84214?lnk=gst&q=sort+by+rows#e3c0d4b271b84214

Cheers
Matt

Excel can do sort right to left rather than up and down.

I tested the following in the oldest Excel I have 97 but it should work
with anything more modern

Add a new row above the top data row you have and fill it with 52 on the
left column down to 1 on the right (or any other order you need to sort by)

Then select your data and the new row and do

data | sort | options | sort left to right | OK | choose row 4 or
wherever your new data is

Works without VBA or programming

 

[Matt Knight]

unfortunately, I can't email the file, primarily because it's highly
confidential and secondly because it's about 45MB in size. But I'll
have a crack at trying to explain better:

Here's a before and after summary of what I need (using theoretical
figures):
http://farm4.static.flickr.com/3452/3837517368_4eb51247ca_o.jpg

The numbers are values at a point in time, and what I need to do is
have all the starts (period 0) at the right and all the ends at their
various points on the left (for reasons I've since forgotten!)
Hopefully this makes some sense. Essentially what I need is to sort
descending by row 1 after column C, so that from Column D onwards, all
the columns are mirrored (D becomes BC, C becomes BB, E becomes AB,
right through until BC becomes D).

Does that make any more sense?

 

[Matt Knight]

Would you believe that I'd looked in that "options" box and missed the
left-to-right option! I often wonder how I manage with even simpler
tasks.

Thanks for the easy solution! I'll try and be less idiotic in future!

Cheers
Matt

 

[Don Guillett]

As has been mentioned you may sort right to left or use CUT/paste in the new
location. You could send a file with all but a few rows removed for size and
security. I see very confidential stuff daily. Also a better before/after
I just recorded this and cleaned up a bit for
a b c 3 2 1 0

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 8/19/2009 by Donald B. Guillett
'

'
Columns("D:bc").Sort Key1:=Range("D1"), Order1:=xlDescending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight,DataOption1:=xlSortNormal
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
unfortunately, I can't email the file, primarily because it's highly
confidential and secondly because it's about 45MB in size. But I'll
have a crack at trying to explain better:

Here's a before and after summary of what I need (using theoretical
figures):
http://farm4.static.flickr.com/3452/3837517368_4eb51247ca_o.jpg

The numbers are values at a point in time, and what I need to do is
have all the starts (period 0) at the right and all the ends at their
various points on the left (for reasons I've since forgotten!)
Hopefully this makes some sense. Essentially what I need is to sort
descending by row 1 after column C, so that from Column D onwards, all
the columns are mirrored (D becomes BC, C becomes BB, E becomes AB,
right through until BC becomes D).

Does that make any more sense?