Sorting columns into a table

  • Thread starter Thread starter Christian
  • Start date Start date
C

Christian

Hi,
I need to sort the following data in A2:C8 to E2:G8 (actually there are 40
rows of data). The date needs to be sorted to Low risk first (then mid and
high) and then by descending sales in each category of risk.

A B C D E F G
1 Risk Name Sales Risk Name Sales
2 Mid Joe 240 Low Mary 630
3 High Jack 540 Low Josh 234
4 Low Mary 630 Mid Joe 240
5 Low John 76 Mid Carol 136
6 High Bess 120 Low John 76
7 Low Josh 234 High Jack 540
8 Mid Carol 136 High Bess 120

I need a function solution as the spreadsheet will be various hands and not
all are comfortable with running macros, pivots or filters.

Many Thanks, Christian
 
Hi Christian

You say that you don't want to use a macro, but I will give it a shot
anyway.

Insert a Command Button from the Control Toolbox menu, then right click on
the button and select View Code. Replace the code in the code sheet that
open with the code below:

Private Sub CommandButton1_Click()
Application.AddCustomList ListArray:=Array _
("Low", "Mid", "High")
Range("A1:C8").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlDescending, Header:=xlGuess, _
OrderCustom:=7, MatchCase:= _
False, Orientation:=xlTopToBottom

Range("D1:F8").Sort Key1:=Range("D2"), _
Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlDescending, Header:=xlGuess, _
OrderCustom:=7, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub

Close the VBA editor. Right click the button again > Properties > Find the
field "Caption" and enter "Submit" right to this field. > Close the
properties window.

Click "Exit Design Mode" on the Control toolbox menu.

Now the user can click the Submit button to sort the table.

Hopes it helpes.

Best regards,
Per
 
Thanks for the macro - with the button it should be pretty foolproof.

sorry for the multiple posting, I had some connection problems and thought
the first postings didn't get through

regards fom Belgium, Christian
 
Hi,

I have a question about sorting/custom sort.

In a worksheet, I have 8 individual tables. Each table has 8 columns and 4
rows. I need to sort each of the table according to the decreasing values of
3 columns. I have no problem with this.

I used the function 'custom sort'. I find that when I apply this to the
second table, the 'custom sort' of the first table vanishes. Altogether, I am
able to get the 'custom sort' into only 1 table. But I need the sorting in
all the 8 and I cant combine them into a single table.

Please help. Thanks in advance.
 
Sun, 16 May 2010 04:39:01 -0700 from [rajath] <[rajath]
@discussions.microsoft.com>:
I have a question about sorting/custom sort.

In a worksheet, I have 8 individual tables. Each table has 8 columns and 4
rows. I need to sort each of the table according to the decreasing values of
3 columns. I have no problem with this.

I used the function 'custom sort'. I find that when I apply this to the
second table, the 'custom sort' of the first table vanishes. Altogether, I am
able to get the 'custom sort' into only 1 table. But I need the sorting in
all the 8 and I cant combine them into a single table.

Please help. Thanks in advance.
Click to expand...

AFAIK, only the most recent custom sort settings are stored, so when
you want to sort a different table you have to enter the settings
again.

I think the thing to do is to create a macro. The macro would sense
which is the current cell, and the would sort the table that contains
that cell.

To create the macro, you can Record while doing a sort, then use that
as a guide to create the other seven sorts. All you have to do then
is add the logic to pick which sort, based on current cell. Or, if
the tables aren't too large, you could perhaps just sort all eight
tables without regard to the current cell.
 
Hi,

Cannot understand what you are saying. I applied a custom sort on two
different tables and both are working fine. Could you explain further

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top