rank the values in a column from highest to lowest

[ahsankhalid]

Hi Guys, 

Here is my dataset and I am trying to create a new Column "Rank 2". This colum will analyze the values in Column E(Rank) for each Framework(Col A). 

The lowest value gets 1 and highest gets 4 in this case but could be as much as 60. 

Please can someone show me how I can get the Rank 2 Column. 

[danilang]

Hi @ahsankhalid 

Here's one way to do it

After removing the original Rank 2 Column, add a RecordID to return your records back to the original order.  Sort by Framework ASC and Rank Desc.  This puts the highest ranks as the first record for each framework and they decrease after that.  Use a Multi-Row tool that increments previous Rank 2 value by one.  Sort by RecordID to get your original order.

Dan

[tonyp]

Use the Tile tool.

Method: Unique values.

Unique Fields: Column E

Grouping Fields: Column A

Output: TileNumber gives you the Rank2 output.

Sample input:

Sample output:

Tool configuration:

[ahsankhalid]

Hi tonyp, 

The output is in ascending order where it give rank 1 to the lowets value. Anyway I can get 1 to the highest value(which is always 1). 

[ahsankhalid]

Sorry guys I think I have written the opposite of what I wanted. I want the highest value(that is always 1) to be ranked 1st and then do descending. 

[danilang]

Hi @ahsankhalid 

It looks like I misread what you miswrote, since my solution gives the answer in the picture.

Dan

[tonyp]

The Tile tool unfortunately doesn't have the option to change the direction of the sequence, although you can get there using this workflow:

Summarise contains Group By and Count of your group column (A).

Join brings the summarised count into your main data.

Formula then reverses the tile order using Tile-num = Count - Tile_num +1

[neilgallen]

While the tile tool does not have an option to change the rank order as @tonyp mentions...you could get the same results by sorting the data before using the tile tool as below.

Tile Settings - make sure you have "leave unsorted" checkedWorkflow Overview