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. YearSubjectCityScoreExpected rank2018AccountingManchester51712018AccountingCardiff32222018AccountingLondon32222018AccountingCambridge4042018AccountingYork3252018AccountingBelfast 2018EnglishChester20212018EnglishBrighton20212018EnglishYork20212018EnglishBelfast1142019AccountingYork50112019AccountingBelfast40022019AccountingChester34532019AccountingBrighton34532019AccountingManchester3352019AccountingCardiff26 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 sample data.xlsx

Rank by group

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

sample data.xlsx

Workflow

Accepted answers

RolandSchubert

Hi @ms_315 ,

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

Best,

Roland

Rank.yxmd

DiegoParker

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

Forum Example.yxmd

All comments

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#M20505

RolandSchubert

Hi @ms_315 ,

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

Best,

Roland

Rank.yxmd

DiegoParker

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

Forum Example.yxmd

ms_315

Great - thank you for this solution!

Emmanuel_G

One possible solution.

Cheers!

Ranking.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335