I've got a file with 3 columns. Group, Value and Rank. Where Rank needs to be determined in the group. If there are duplicate values in a group, they would be ranked 1, 2, 2, 3, 3, 4, 5 and so on.
I was able to use the following formula below to get me half way to the solution. But it does not account for duplicate values in the way I would like.
=SUMPRODUCT(($A$2:$B$21=A2)*($B$2:$B$21>B2))+1
This is just a sample set of data... the actual data will have about 50,000 rows and hundreds of groups of variable size. Not sure if that will make a difference in how the problem is approached. But I thought I might mention that.
<tbody>
</tbody>
Hopefully I was clear in explaining my problem. I'm pretty new to excel. Thank you in advance for help! Let me know if I can do anything to clarify my problem further.
I was able to use the following formula below to get me half way to the solution. But it does not account for duplicate values in the way I would like.
=SUMPRODUCT(($A$2:$B$21=A2)*($B$2:$B$21>B2))+1
This is just a sample set of data... the actual data will have about 50,000 rows and hundreds of groups of variable size. Not sure if that will make a difference in how the problem is approached. But I thought I might mention that.
Group | Number | Rank |
1 | 97 | |
1 | 65 | |
1 | 60 | |
1 | 46 | |
1 | 98 | |
1 | 53 | |
1 | 74 | |
2 | 9 | |
2 | 33 | |
2 | 35 | |
2 | 86 | |
2 | 86 | |
3 | 39 | |
3 | 97 | |
3 | 99 | |
3 | 41 | |
3 | 60 | |
3 | 97 | |
3 | 34 | |
3 | 96 |
<tbody>
</tbody>
Hopefully I was clear in explaining my problem. I'm pretty new to excel. Thank you in advance for help! Let me know if I can do anything to clarify my problem further.