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

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

Data sorting automatically?
 

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

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

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

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"

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"

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.

Data sorting automatically?
 

Thanks for 'Sorting' this out!

Tommy

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

Data sorting automatically?
 

I tried the code but could not get it to work.
But the formula works perfect.
Thanks