A challenge I encountered when coming to grips with Alteryx was to rank records based on a shared value.
For this exercise, we are trying to rank players according to their score. Simply sorting and ranking the records would give players with the same score different ranks – what if we want players with the same score to share the same rank?
Or in broader terms, how can I give all records that share a certain value the same rank?
![](https://www.thedataschool.co.uk/content/images/wordpress/2018/10/Unranked-173x300.png)
[count] refers to players’ scores.
For that purpose we insert a Mulri-Row Formula Tool. Check “Create New Field” and name it appropriately – for our exercise, we name the field “Rank”. Then make sure you have selected “0 or empty” for Rows that don’t Exist. In the expression box, insert the following if-statement:
if [count] != [Row-1:count] then [Row-1:Rank] + 1 else [ROW-1:Rank] endif
![](https://www.thedataschool.co.uk/content/images/wordpress/2018/10/Multi-Row-282x300.png)
Multi-Row Formula
Because we selected “0 or Empty” for rows that don’t exist, Alteryx assumes there is a hypothetical row above the first whose value is 0. That’s what the formula will reference in the very first row, thus the first value in the new Rank column will be (0+1) = 1.
![](https://www.thedataschool.co.uk/content/images/wordpress/2018/10/Ranked-219x300.png)
Players Ranked according to Score