Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
Every day I have to input the number of meal types served in a restaurant.
This is to keep a list of the best and worse sellers. For example: Roast beef 12 Roast Lamb 9 Steak 8 Ham Salad 2 There is a total of 57 meals on the menu and each days count is added to the previous days total. Two columns are used A is Meal name and B is total sold. I have to highlight the two columns then select 'Data' then 'Sort' everytime I want to show the latest best sellers. Is there a way to get the two columns to sort automatically, as the data is inputted. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
Tommy,
In the sheet module for that sheet, copy and paste this macro: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Note that this is a Worksheet_Change event not a Worksheet_SectionChange event, Just delete the Worksheet_SelectionChange before pasting in this one. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Tommy" wrote in message ... Every day I have to input the number of meal types served in a restaurant. This is to keep a list of the best and worse sellers. For example: Roast beef 12 Roast Lamb 9 Steak 8 Ham Salad 2 There is a total of 57 meals on the menu and each days count is added to the previous days total. Two columns are used A is Meal name and B is total sold. I have to highlight the two columns then select 'Data' then 'Sort' everytime I want to show the latest best sellers. Is there a way to get the two columns to sort automatically, as the data is inputted. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
Forgot to say to get into the sheet module right-click on the sheet tab and
select "View Code" -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... OOPs!Make that: Sub Macro2() Dim EndData As Long Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = True End Sub Although I believe that the screenupdating is automatically restored on exiting from the Macro - at least in XL97 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... Tommy, In the sheet module for that sheet, copy and paste this macro: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim EndData As Long If Target.Column < 2 Then Exit Sub Application.ScreenUpdating = False EndData = Cells(Rows.Count, 1).End(xlUp).Row With Range(Cells(2, 1), Cells(EndData, 2)) .Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Application.ScreenUpdating = False End Sub Note that this is a Worksheet_Change event not a Worksheet_SectionChange event, Just delete the Worksheet_SelectionChange before pasting in this one. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Tommy" wrote in message ... Every day I have to input the number of meal types served in a restaurant. This is to keep a list of the best and worse sellers. For example: Roast beef 12 Roast Lamb 9 Steak 8 Ham Salad 2 There is a total of 57 meals on the menu and each days count is added to the previous days total. Two columns are used A is Meal name and B is total sold. I have to highlight the two columns then select 'Data' then 'Sort' everytime I want to show the latest best sellers. Is there a way to get the two columns to sort automatically, as the data is inputted. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
You could try using 2 other columns to take your original datalist, where
you type in your recent numbers, and use formulas to display *instant* updated, sorted data. Say datalist in A1 to B57 Enter this formula in say C1: =LARGE($B$1:$B$57,ROW()) AND, enter this *array* formula in D1: =INDEX(A$1:A$57,SMALL(IF(B$1:B$57=C1,ROW($1:$57)), COUNTIF(C1:$C$57,C1))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. NOW ... After the CSE entry, select *both* C1 and D1, and drag down to copy to row 57. Any number changed/entered in Column B will automatically, instantly revise the *entire* datalists in Columns C & D. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Tommy" wrote in message ... Every day I have to input the number of meal types served in a restaurant. This is to keep a list of the best and worse sellers. For example: Roast beef 12 Roast Lamb 9 Steak 8 Ham Salad 2 There is a total of 57 meals on the menu and each days count is added to the previous days total. Two columns are used A is Meal name and B is total sold. I have to highlight the two columns then select 'Data' then 'Sort' everytime I want to show the latest best sellers. Is there a way to get the two columns to sort automatically, as the data is inputted. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
Sandy posted two sets of code. The first was event code, the second was a macro
that would be run by hitting a button or Alt + F8 and Run. I think he meant for both to be Event code Event code goes into the sheet module. The macro goes into a general module A caveat should be mentioned if using the Event code Autosort leaves no room for making mistakes in data entry. If you misspell a word and hit enter that misspelled word is sorted with the rest of the data. Could make it hard to find later. Gord Dibben MS Excel MVP On Wed, 21 Mar 2007 14:20:36 -0000, "Sandy Mann" wrote: Forgot to say to get into the sheet module right-click on the sheet tab and select "View Code" |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
"Gord Dibben" <gorddibbATshawDOTca wrote in message
... Sandy posted two sets of code. The first was event code, the second was a macro that would be run by hitting a button or Alt + F8 and Run. I think he meant for both to be Event code Thank sfor pointing that out Gord - I never even noticed that I copied the wrong code the second time. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Sandy posted two sets of code. The first was event code, the second was a macro that would be run by hitting a button or Alt + F8 and Run. I think he meant for both to be Event code Event code goes into the sheet module. The macro goes into a general module A caveat should be mentioned if using the Event code Autosort leaves no room for making mistakes in data entry. If you misspell a word and hit enter that misspelled word is sorted with the rest of the data. Could make it hard to find later. Gord Dibben MS Excel MVP On Wed, 21 Mar 2007 14:20:36 -0000, "Sandy Mann" wrote: Forgot to say to get into the sheet module right-click on the sheet tab and select "View Code" |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
That's why there is a great herd of us monitoring these groups.
We can also monitor each other. I shudder to think of the havoc wreaked if I alone were providing assistance. I never use a spell-checker and sometimes my pre-post testing is done on spurious material which makes me think my test is correct when it is not. Gord On Wed, 21 Mar 2007 18:16:38 -0000, "Sandy Mann" wrote: "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Sandy posted two sets of code. The first was event code, the second was a macro that would be run by hitting a button or Alt + F8 and Run. I think he meant for both to be Event code Thank sfor pointing that out Gord - I never even noticed that I copied the wrong code the second time. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
Thanks for 'Sorting' this out!
Tommy |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
You're welcome ... I think ?!?!
Did you use the formulas (this *IS* the functions group), or did you use the code?<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Tommy" wrote in message ... Thanks for 'Sorting' this out! Tommy |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data sorting automatically?
I tried the code but could not get it to work.
But the formula works perfect. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data & Updating Automatically | Excel Discussion (Misc queries) | |||
Sorting Data Automatically | Excel Worksheet Functions | |||
Automatically sorting data | Excel Discussion (Misc queries) | |||
Sorting data automatically | Excel Worksheet Functions | |||
Sorting Data Automatically | Excel Discussion (Misc queries) |