Automatic Sorting?

[Scott B. Hogle]

Is there a way that Excel can automatically sort a column?

I am working on a spreadsheet where the end user "cut and
pastes" data from an outside source to Excel. The Excel
spreadsheet then manipulates the data and creates a one
page presentation piece.

One of the features on the presentation piece is a "Top
10 Client" listing - this is why I want automatic sorting.

I know how to sort the data manually, using the Data -
Sort technique, but I was hoping I wouldn't have to train
the end users how to perform that step.

Any ideas?

Thanks in advance for your help.


Scott B. Hogle

 

[Tom Ogilvy]

turn on the macro recorder, then do a manual sort.

Turn off the macro recorder.

This will show you the code you need to do the sort.

put this code in the worksheet change event and put in a condition that
"target" is in the range you want sorted.

for example:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

 

[Bob Phillips]

Scott,

You can do it without sorting.

On the summary page, for the Top 10, you can get the value like so

=LARGE(Sheet1!A:A,ROW(A1))

and just copy down 9 rows.

If you want some related data, use a formula like

INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Hi Scott,
If you try for completely automatic, it will interfere with you
entering data as you try to change the next cell. I just
created a pair of worksheet macros for someone else today,
see Sorting triggered by an Event Macro (#activate)
http://www.mvps.org/dmcritchie/excel/sorting.htm#activate
The event macros are Worksheet_Activate and Worksheet_BeforeDoubleClick

Actually after rereading your post, perhaps the topic above that one.

Seems strange another person also mentioned copying and
pasting from HTML you might as well check my reply to Ron.
http://google.com/[email protected]
until Google slurps it up in twelve hours you may have to look here

 

[David McRitchie]

Hi Bob,
nice resolution / interpretation of what appears to be
really wanted by a means other than sorting. -- David

 

[Bob Phillips]

Thank-you David, appreciated.

Bob

Hi Bob,
nice resolution / interpretation of what appears to be
really wanted by a means other than sorting. -- David

 

[Scott B. Hogle]

Thanks to everyone!

I used Bob's suggestion and it worked great. I never
would have figured that out on my own.

I still don't know how the INDEX Function works, but I
was able to use Bob's coding and now the client name and
sales results are tied together.

Thanks again, you guys are awesome!


SBH

 

[Bob Phillips]

Hi Scott,

INDEX, as help says, returns the value of an element in a table or an array,
selected by the row and column number indexes. So it is similar to the
LOOKUP functions but finds the value based upon a row and column index
rather than a value. Because of this, it is often combined with MATCH which
does lookup a value, but returns the row number.

So in the example I gave

MATCH(A1,Sheet1!A:A,0)

finds the row within the column of our value and returns the row number, and
then

INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))

returns the value in column B for our row. In the example I gave, VLOOKUP
would also have worked

=VLOOKUP(A1,Sheet1!A1:B11,2,FALSE)

but INDEX & MATCH works better when the data is not organised with the key
value to the left. In the example I gave I felt that the data was more
likely to be something like

Client Id || Client Name || some other data || value || etc

so the lookup is on value, INDEX and MATCH can handle this.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[tristan bond]

How about 2 or more fileds(different name) have same sorting criteria?
After using index(A:A, match(A:A,xxxx), they share same name.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[William]

I also want to thank Bob. This is something I've been scratching my head
over for a week or so. Finally resorted to searching on the Internet
rather than the masochistic pleasure of trying to work it out for my
self.

So far I've only tried Bob's original suggestion (LARGE / INDEX) and
it's great. It is true that if you have two results exactly the same
then there is a problem.

With my needs I am happy to add a very small, unique (0.000x) number to
the original. Therefore every number will always be slightly different,
then I can just show the final number to only 2 decimal places.

Regards

William


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!