I have a problem ranking a large dataset(more than 30000 rows, 16 different columns need to be ranked). My problem is that I dont want the ranks to have gaps when there are ties.
See how it should be in table below.
<tbody>
</tbody>
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly. Maybe someone here can help, it would be greatly appreciated
Thanks
See how it should be in table below.
Ext P$ | Rank | Should be |
2,128.34 | 1 | 1 |
2,128.34 | 1 | 1 |
1,023.78 | 3 | 2 |
822.72 | 4 | 3 |
<tbody>
</tbody>
I do have a working solution with an array formula similar to this, but it slows down my macro (30 minutes instead of 10 seconds) as I need it to calculate 16 times
Code:
=SUM(1/COUNTIF(A$2:A$35000;A$2:A$35000)*(A$2:A$35000>A2))+1
I was thinking of using a for next loop to rank sorted columns but I dont know how to set it up properly. Maybe someone here can help, it would be greatly appreciated
Thanks