Data sorting automatically?

[Tommy]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[RagDyeR]

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!
=====================================================


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

 

[Gord Dibben]

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

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Gord Dibben]

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

 

[Tommy]

Thanks for 'Sorting' this out!

Tommy

 

[Ragdyer]

You're welcome ... I think ?!?!

Did you use the formulas (this *IS* the functions group), or did you use the
code?<bg>

 

[Tommy]

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