How do I have Excel autosort a list of rankings?

[nygiants242]

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!

 

[pplstuff]

Vlookup off your rank.

http://www.auditexcel.co.za/Automatic Sorting Project.html

 

[nygiants242]

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

 

[pplstuff]

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.

 

[nygiants242]

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.

 

[katrine06]

Hi,

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

Katrine

 

[pplstuff]

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.

 

[nygiants242]

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!

 

[nygiants242]

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

 

[nebffa]

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

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H1:H7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H7")
        .Header = xlGuess
        .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*")