A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sorting columns into a table



 
 
Thread Tools Display Modes
  #1  
Old July 20th, 2008, 01:56 PM posted to microsoft.public.excel.worksheet.functions
Christian
external usenet poster
 
Posts: 74
Default Sorting columns into a table

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

  #2  
Old July 20th, 2008, 02:04 PM posted to microsoft.public.excel.worksheet.functions
Wigi
external usenet poster
 
Posts: 292
Default Sorting columns into a table

And yet again, see your other posts, I already responded.

Please do not multipost.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Christian" wrote:

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

  #3  
Old July 20th, 2008, 02:23 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default Sorting columns into a table

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

"Christian" skrev i meddelelsen
...
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


  #4  
Old July 20th, 2008, 06:07 PM posted to microsoft.public.excel.worksheet.functions
Christian
external usenet poster
 
Posts: 74
Default Sorting columns into a table

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
  #5  
Old May 16th, 2010, 12:39 PM posted to microsoft.public.excel.worksheet.functions
[rajath]
external usenet poster
 
Posts: 1
Default Sorting columns into a table

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.
  #6  
Old May 16th, 2010, 03:38 PM posted to microsoft.public.excel.worksheet.functions
Stan Brown
external usenet poster
 
Posts: 536
Default Sorting columns into a table

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.


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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
  #7  
Old May 19th, 2010, 12:32 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Sorting columns into a table

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

"[rajath]" wrote in message
...
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.