sorting vertically.

[ibbm]

I have a spreadsheet that I need to have the columns sorted by instead of
rows.

I have data like

Store Name ABC Store DEF Store GHI Store etc etc

Fruit Sales 100 150 200
Veg Sales 60 75 50

Total Sales 160 225 250



What I want to see is the top 10 stores by total sales so it would look like

GHI Store DEF Store ABC Store

Fruit Sales 200 150 100
Veg Sales 50 75 60

Total Sales 250 225 160

Can this be done?

 

[JMB]

One possibility: You could copy, edit/pastespecial/transpose the data to an
empty worksheet. Sort it by what is now the total column. Then
copy/pastespecial-transpose back to the original location. This can easily
be done manually.

I also included some code - but there are some assumptions made. It assumes
you select your data (excluding the far left column w/the descriptions - when
the data gets transposed these would become headers and the sort code assumes
no headers. You can change this, but the sort code has to also change to
recognize the header), then run the macro.

It also assumes the Total amount is the last row of data that you select.
When transposed, it is the last column which is used by the sort code.

Be sure to make backup copies!


Sub Sort()
Dim WkSht As Worksheet
Dim CopyRange As Range

Set CopyRange = Selection
Set WkSht = Worksheets.Add

CopyRange.Copy
WkSht.Range("A1").PasteSpecial Paste:=xlPasteAll, _
operation:=xlNone, skipblanks:=False, _
Transpose:=True
Application.CutCopyMode = False

With WkSht
'Sort by first row, last column - assumes no headers
.UsedRange.Sort Key1:=.Cells(1, .UsedRange.Columns.Count), _
Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

WkSht.UsedRange.Copy
CopyRange(1, 1).PasteSpecial Paste:=xlPasteAll, _
operation:=xlNone, skipblanks:=False, _
Transpose:=True
Application.CutCopyMode = False
Application.DisplayAlerts = False
WkSht.Delete
Application.DisplayAlerts = True

End Sub

 

[NickHK]

ibbm,
Manually, click the "Options" button at the bottom of the Data>Sort dialog.
select "Left to Right".
In code, the recorder generates something like this:
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

NickHK

 

[JMB]

I never noticed there was an options button there.