Macro to Custom Sort a single column?

[Peter h]

I know how to sort a table of data based on different criteria, but I was wondering if there is a way I can custom sort a single column based on multiple criteria from other columns. I'd like it in a macro, because I want it to update the sort every time I submit data from a userform.

Here's what I got:

My worksheet has 4 columns. Starting in A: Player Names, Wins, Losses, and Rank. I don't want to rearrange the order of A, B, or C, because then it messes with some of the code I have written and some of the controls in my Userform. So, I want Column D (Rank) to be rearranged based on this criteria: First Wins-Descending (Column B), Second Losses-Ascending (Column C). So it should look something like this:

It would start like this
Player Name__Wins__Losses__Rank
Player1_______0______0_____1
Player2_______0______0_____2
Player3_______0______0_____3

And then after a few matches it would look like this.
Player Name__Wins__Losses__Rank
Player1_______2______1_____2
Player2_______1______2_____3
Player3_______3______0_____1

Thanks for any help.

 

[Joe4]

I don't think you want a custom sort, I think you want to use the RANK function (see: https://www.techonthenet.com/excel/formulas/rank.php)
So if your data was in A1:D4, here is the formula to place in D2 and copy down:

=RANK(B2,B$2:B$4)

 

[Peter h]

I don't think you want a custom sort, I think you want to use the RANK function (see: https://www.techonthenet.com/excel/formulas/rank.php)
So if your data was in A1:D4, here is the formula to place in D2 and copy down:

=RANK(B2,B$2:B$4)

Thanks. I actually didn't know about this function. I'm trying to convert it to VBA though, and I'm not having much luck. Here's what I've got pieced together form other things I've found on the net:

Sub RankMe()
Dim ws As Worksheet
Dim lR As Long
Dim a As Integer
Set ws = ThisWorkbook.Sheets(1)
lR = ws.Range("A" & Rows.Count).End(xlUp).Row
    For a = 2 To lR
        ws.Cells(a, 4) = Application.WorksheetFunction.Rank(Cells(a, 2), Range("B1:B" & lR, "C1:C" & lR), 1)
    Next a
End Sub

First off, how can I change the rank to Descending rather than Ascending?

Second, How do I add the second criteria? If I change the range to only look at 1 column (Wins) it will rank them just fine, but as soon as I try to add the second column of criteria (Losses) it gives me weird rankings.

Third, If 2 players have the same win/loss record, I don't want them to have the same rank. If I have 8 players, there should be ranks 1-8, even if some records are tied.

 

[Joe4]

I'm trying to convert it to VBA though, and I'm not having much luck

Note that by doing that, you are hard-coding the result. That means if the data changes, the rankings won't, unless you re-run your macro.
Unless you are doing some very complex ranking which cannot be expressed by formulas, you might be better off to have VBA paste the actual RANK formula instead of just the result. Then it will be dynamic and automatically change as your data changes.

First off, how can I change the rank to Descending rather than Ascending?

The link I sent shows you that. It is the third argument. Choose 0 instead of the 1 you are using.

Second, How do I add the second criteria? If I change the range to only look at 1 column (Wins) it will rank them just fine, but as soon as I try to add the second column of criteria (Losses) it gives me weird rankings.

Are they not going to have the same number of total games played?
If so, than anyone with the same number of wins will have the same number of losses. So the point is moot in that case.
If not, then how exactly show that rank work? What is Player1 has 3 wins and 2 losses and Player2 has 2 wins and 1 loss. Who should be ranked higher?

Third, If 2 players have the same win/loss record, I don't want them to have the same rank. If I have 8 players, there should be ranks 1-8, even if some records are tied.

So, in cases of ties, what is the logic for determining who are getting the higher rankings?

 

[Peter h]

Note that by doing that, you are hard-coding the result. That means if the data changes, the rankings won't, unless you re-run your macro.
Unless you are doing some very complex ranking which cannot be expressed by formulas, you might be better off to have VBA paste the actual RANK formula instead of just the result. Then it will be dynamic and automatically change as your data changes.

Ya, I had thought about this. I will be running the macro each time I submit new data from my userform. The only reason I didn't want to use a formula was because I won't always know how many rows of data I will have. I'm far less confident in my ability to write and understand formulas than I am with writing the code that accomplishes the same thing.

The link I sent shows you that. It is the third argument. Choose 0 instead of the 1 you are using.

I had been messing around with my project and doing some googling before I actually read the link you shared. I read it after I replied to your comment. I apologize for that.

Are they not going to have the same number of total games played?
If so, than anyone with the same number of wins will have the same number of losses. So the point is moot in that case.
If not, then how exactly show that rank work? What is Player1 has 3 wins and 2 losses and Player2 has 2 wins and 1 loss. Who should be ranked higher?

I guess that is something I forgot to mention. They may not always play the same number of games. Each match will be a Best-of-3 match. So if each player plays 3 matches, there is a maximum of 9 games they can play but a minimum of 6 games. So after the first match Player 1 could have a record of 2-0 and Player 2 could have a record of 2-1. Player 1 would be ranked 1 and Player 2 would be ranked 2 because that player has a loss.

So, in cases of ties, what is the logic for determining who are getting the higher rankings?

I guess I'm not exactly sure. lol. I think this part will take a bit more thinking on my part. I could maybe try to factor in some sort of Strength-of-schedule type factor that might work as a tie breaker.

 

[Peter h]

I think I figured out how I can do it. I added another column for "Win Ratio". So now it automatically calculates the win ratio as I enter game records, and then I calculate the rank from win ratio. So a player with a 2-1 record is ranked lower than a player with a 2-0 record. I'll figure out tie-breakers later, after I think about how I want to do it.