# How do I have Excel autosort a list of rankings?

#### nygiants242

##### New Member
Here is a snapshot of my spreadsheet:
http://tinypic.com/view.php?pic=mb47kk&s=6

On my spreadsheet, D, E, and F represent differnet variables. When I assign them different weights, the overall "ranking" of each of the cities then changes. How can I have Excel automatically keep Column H ranked in order so that the cities are always displayed from 1 to 30, in order?

For example, if I change D, they will be all out of order and remain in that order. I need the cities to adjust accordingly after changing the weights and number - essentially column H's rank to be correct, and for the cities to move with them.

Thanks for any help - much appreciated!

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
I couldn't really figure that out, and then I would need to change the format and arrangements of the cells. I want to keep it the way it is.

Any other ways? I'm a beginner

Basically, you's leave the table just as it is - except put the rank in column A (or whatever the first column is in your table).

Then you'd make a second table, with column A starting at 1 and then going to 30. then use the vlookup function to populate this entirely new table. This new table will always be sorted. (So you'd enter new data in the old table, and the new table would update automatically).

If you post sample data, we can give you formulas. If you want to expand your excel knowledge, try entering vlookup and using the built in MS Help to declare the criteria.

There may be a solution using VBA; however, I have no solution for this.

That is the data right there. The ranks in column D, E, and F essentially is the data. The Weighted Factors are multiplied by each column and added together. The lowest total is equal to a Rank of 1. The highest total is equal to a Rank of 30. For example, Detroit is overall Rank 1 because (0.4)*(2) + (0.2)*(5) + (0.4)*(2) is the lowest score, hence it is Ranked 1.

Data is on the pic. Does that allow you to give me more specific instructions? Thanks a lot in advance.

Hi,

I think if you select all the columns and then click the sort options then it works like that.

Katrine

Insert the overall rank into B:B
In a new sheet A1 = 1, A2 = 2, A3 = 3...
b2=vlookup(\$a1,sheet1!\$b\$4:\$f\$32,column(),0)
^^^Drag accross and down.
If you move the new table, you'll need to change the "column()" reference.

Katrine - I want it to do it automatically. I realize you can do that manually, but I don't want to do it everytime

pplstuff - I don't exactly understand that? I don't get what is so hard about this haha. All I want is for Excel to auto-order!

Still need help with this! Help guys.. I could even post the file (somehow?) if you guys want.. Do you need any more explanation?

Hi nygiants242 - this is the kind of thing you are looking for.

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:H7")
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub``````

What this does is perform a sort whenever anything on a particular worksheet is changed.

Two important points:

Q. Where to put this code?
A. Go to the Visual Basic Editor (Alt + F11). In the upper left window, select the worksheet you wish to use, then copy and paste the code in the main window.

Q. Do I need to change any of this code?
A. Yes - several parts of it. I will list them:

Line 2 - "Sheet1" must be changed to "*NameOfYourSheet*"
Line 3 - "Sheet1" must be changed to "*NameOfYourSheet*"
Line 3 - Range("H1:H7") must be changed to Range("*which cells in column H you are sorting*")
Line 5 - "Sheet1" must be changed to "*NameOfYourSheet*"
Line 6 - Range("A1:H7") must be changed to Range("*which cells in your sheet you are sorting*")

Replies
5
Views
256
Replies
2
Views
128
Replies
1
Views
169
Replies
3
Views
323
Replies
3
Views
144

1,203,068
Messages
6,053,346
Members
444,654
Latest member
Rich Cohen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back