Auto Sort A List by Values in Excel

[Andrew in Sydney]

Hi fom Sydney!

Is there a way in Excel to have a list of names with values automatically re-sort whenever any of the values change.

The default sort I have in mind is ascending by values.

eg.

CITY VALUE
Sydney 100
Boston 200
London 300
Moscow 400
Houston 500

The values change to:

Sydney 5000
Boston 250
London 8000
Moscow 300
Houston 50

I now want Houston 1st, Boston 2nd etc - automatically - without me having to do a sort again by Value.

Any help with this highly apreciated.

Andrew Lawless
Sydney - Australia

 

[Tom Urtis]

Yes, the VLOOKUPs would be an issue, insofar as the event trigger, which would be a Calc event.

Off the top of my head, this should work; if not please repost. Replace the previous 2 procedures I posted with this:



Private Sub Worksheet_Calculate()
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.count, 12).End(xlUp))
SortRange.Sort Key1:=Range("L2"), Order1:=xlAscending, Key2:=Range("K2"), Order2:=xlAscending, Header:=xlYes
End Sub

 

[Andrew in Sydney]

That's excellent Tom.
Works like a dream.
Thanks so much.
Andrew

 

[Warnecke]

Hi Tom,

I have followed your directions in this thread, but I cannot seem to get the results I want.

This is my problem:
I have two sheets - one that calculates returns for different people and another sheet that contains direct reference to the returns from the first sheet. Thus, in the second sheet I have a list containing person and return. I would like to have this second sheet sortet automatically according to return (highest to lowest) every time I open the workbook. The first sheet has a link to Bloomberg, so everytime I open the workbook I will get a new return for (probably) all persons.

Thanks...

 

[ablazes]

I hate merged cells.

See if this does what you want, in place of the first one I sent:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Cells.count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.count, 12).End(xlUp))
SortRange.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
End Sub

Hi Tom,

I have a very similar question as the OP. Instead of 2 columns, I have 5. My data is from A1:E15, where row 1 has headers. I'm trying to sort 1st by column E and then by A.

I'm a noivice at this. After I paste the VBA code save my spreadsheet, nothing happens. Is there another action I need to perform?

Please advise.

Thanks!

 

[JOR]

is there a way to do the simplified version of this WITHOUT using VB ?

thanks
JR

 

[r71lima]

[h=1]Auto sort results of lookup formula[/h]I need to Auto Sort the information below. It is populated through lookUp formulas and calculations based on the lookup brought over from another tab of information. My cells are Cells H1 through N15. The rank portion of the chart needs to sort to decending order each time this data is carried over and calculated. How can I do that. the sort should be based on the %Participating column. (Excel 2003 worksheet)
Typed Data, Typed Data, Lookup formula, Lookup Formula, Lookup Formula, Calculation of # Dlrs & 3 eligible, Rank Formula

District Performance
District	DM	# Dlrs P059	$ Amt Enrolled	# of Eligible Dlrs	% Participating	Rank
L	Mo	2	$6,129.09	9	22%	1
M	OPEN	2	$17,988.76	9	22%	1
C	Van	1	$3,569.99	11	9%	3
A	Cris	0	$0.00	11	0%	4
B	Erin	0	$0.00	11	0%	4
D	Robert	0	$0.00	11	0%	4
E	Cory	0	$0.00	10	0%	4
F	George	0	$0.00	11	0%	4
G	Tracy	0	$0.00	11	0%	4
H	David	0	$0.00	10	0%	4
J	Lee	0	$0.00	9	0%	4
K	Donald	0	$0.00	8	0%	4
N	Kevin	0	$0.00	8	0%	4

<TBODY>
</TBODY>

 

[pwarrenx]

HI Tom, yet another rookie I`m afraid, tried tweeking your formula to suit my similar excel needs.
Mine Is simply 3 columns, A1:A2 are descriptive and A3 is my data values to be auto sorted. How do i tweek please ?
pwarrenx.

I hate merged cells.


See if this does what you want, in place of the first one I sent:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 12 Or Target.Cells.count > 1 Then Exit Sub
Dim SortRange As Range
Set SortRange = Range(("A1"), Cells(Rows.count, 12).End(xlUp))
SortRange.Sort Key1:=Range("L2"), Order1:=xlAscending, Header:=xlYes
End Sub

 

[Tom Urtis]

A tip with a question, and no problem, we were all rookies at one time. Is it possible for you to use only one cell in row 1 to contain each header label, instead of two cells as you are doing now. For example, if in cell A1 you have First and in cell A2 you have Name, you really should, when setting up a table in Excel, use just one cell that contains the term First Name. If you want the word Name to be underneath the word First in the same cell, you can accomplish that by typing the word First, then pressing Alt+Enter, then typing the word Name and hit Enter. That way, you will have one header row with unique labels as a good table design would have, and the sorting action would be more reliable. If this is not possible for you to do, for whatever reason, please post back for the code that would alternatively be useful to you with two rows serving as collective header labels. But I do suggest again that a wise course of design is to only use one row for headers. And by the way, many thanks for doing the search for your question as you did, on a thread that's 10 years old; believe me, that's always appreciated.

 

[scorpio61]

Hi Tom,

Could you help with my query please,

I am looking for a similar formula, I have a spread sheet we update with personnel names, numbers, trade and employer number as attached.

This is a pivot table and when entering the name, the name is added to the table and the rest is manually added. Then we have to select all columns and auto sort by AZ as we have over 100 entries. The result we achive is by first name alphabetically.

None of the cells are merged,

Can you advise please, I know a little about macro's but not enough yet, Thanks Ian

NAME​

<TBODY>
[TD="width: 24%"]

PERSONNEL NUMBER​

[/TD]
[TD="width: 22%"]

DISCIPLINE​

[/TD]
[TD="width: 28%"]

EMPLOYEE NUMBER​

[/TD]

[TD="width: 26%"]

Dave Smith​

[/TD]
[TD="width: 24%"]

1170284​

[/TD]
[TD="width: 22%"]

Freewill​

[/TD]
[TD="width: 28%"]

0​

[/TD]

[TD="width: 26%"]

Andy Ford​

[/TD]
[TD="width: 24%"]

1213406​

[/TD]
[TD="width: 22%"]

Windmill​

[/TD]
[TD="width: 28%"]

124426​

[/TD]

</TBODY>

 

[Tom Urtis]

Just so I'm clear, are the example names of Dave Smith and Andy Ford in the same cell? If so, what column? If not, what column are the first names in, and what column are the last names in? And also, just to be sure, your data depiction example is that of the source data range, and not the pivot table itself?