Ranking based on 3 columns (values)

**jendan** · 07-12-2018, 05:14 AM

Dear experts in macros,

I would like to kindly ask you for your advise and help.
I need to the ranking based on 3 columns (values).

Rank values from the first column (B) in the descending matter (that means : the highest value has the rank 1.)
If values in first column (B) are the same, then rank values in second column (C). Here the same, if higher, then rank as 1.
If values in first column (B) and second column (C) are the same, then rank values in third column (D). Again, if higher, then rank as 1.

Explanation. Table is for tenis tournament, column B is for Points per wins (calculated by some my metrics), column C is for difference of Sets (win - lost) and column D is for difference of Games (win - lost).
Here is image which describes how it looks like and also manually done ranking how the rating should looks like.
screen.png

Many thanks for your help and share your great knowledge and experience

**AliGW** · 07-12-2018, 05:15 AM

Must this be a VBA solution? If so, I will move the thread.

**jendan** · 07-12-2018, 05:20 AM

No, please Formula.

**AliGW** · 07-12-2018, 05:23 AM

So why do you need members who are expert in macros???

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**jendan** · 07-12-2018, 05:30 AM

Sorry, my mistake in titeling of masters here.
Please cancel this thread.

**AliGW** · 07-12-2018, 05:31 AM

There is no need to cancel the thread! Just provide the workbook and we shall take it from there.

**jendan** · 07-12-2018, 05:35 AM

I really Appologise. It seems to be that I found the solution. I hope.
Really sorry for creating new thread. If possible, please remove this thread to not make mess here.
Many thanks for your support.

**AliGW** · 07-12-2018, 05:38 AM

No, the thread will not be deleted, and if you have found a solution, you should post it here for the benefit of anyone else looking for a similar solution.

I was merely trying to clarify what you were looking for.

**davsth** · 07-12-2018, 05:42 AM

With a helper column, if your data is representative points*100+sets*10+games should give a column that can be ranked with the rank function to get what you desire. AS you have not attached a sheet of given references I can not be more explicit with a formula, it may be b2*100+c2*10+d2

**AliGW** · 07-12-2018, 05:43 AM

The OP has found a solution, which I have asked him/her to post.

**Glenn Kennedy** · 07-12-2018, 06:04 AM

I've been away doing other things, forgetting to post my offering. I'm posting it now, just in case it's useful to someone else. I randomised the rows a bit, as the OP's sample was already in rank order.

=SUMPRODUCT(--((($B$2:$B$8*100)+(C$2:$C$8*10)-$D$2:$D$8)>=(B2*100)+(C2*10)-D2))