Rank by group

[ms_315]

Hi everyone,

I want to create a rank field which groups based on other fields. However, where the scores the ranks are based on are equal i'd like the ranks to be equal too, and for the next rank to begin at the count of previous ranks +1. Here is some example data, with the final column showing how i'd like my rank field to operate. Each City is ranked based on their score, but the rankings are grouped by year and subject.

Year	Subject	City	Score	Expected rank
2018	Accounting	Manchester	517	1
2018	Accounting	Cardiff	322	2
2018	Accounting	London	322	2
2018	Accounting	Cambridge	40	4
2018	Accounting	York	32	5
2018	Accounting	Belfast
2018	English	Chester	202	1
2018	English	Brighton	202	1
2018	English	York	202	1
2018	English	Belfast	11	4
2019	Accounting	York	501	1
2019	Accounting	Belfast	400	2
2019	Accounting	Chester	345	3
2019	Accounting	Brighton	345	3
2019	Accounting	Manchester	33	5
2019	Accounting	Cardiff	2	6

I've seen some similar solutions, but none where the rank after the joint rank starts at the count of the previous ranks +1. I hope this makes sense! I'd be really grateful for any solutions to this.

Thank you,

Matt

[joshuaburkhow]

This is nearly similar to this post from a couple years ago 🙂 Does this solve your issue? 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Ranking-by-Group-in-Alteryx/m-p/55235#...

[RolandSchubert · Accepted solution]

Hi @ms_315 ,

the Multi-Row Formula tool is your friend here. I've attached a sample workflow.

Best,

Roland

[DiegoParker · Accepted solution]

Hi @ms_315 ,

Find my approach attached:

Be mindful of the grouping in the sort and multi-row formulas.

Hope this helps If does, can I ask you to mark it as a solution? this will help other users to find it and will allow us to close the thread. Many thanks!

Best,
Diego

[ms_315]

Great - thank you for this solution!

[Emmanuel_G]

One possible solution.

Cheers!