Formula help

System

This discussion was created from comments split from: Sequential numbering of duplicates.

Gia Thinh

Hi @Matthew Campbell,

Is the sorting in the Ascending Sort column is what you want ? If so, you can try the following column formula :

=COUNTIF(Ranking:Ranking, <Ranking@row) + COUNTIFS(row:row, <row@row, Ranking:Ranking, =Ranking@row) + 1

Hope this works for you!

Matthew Campbell

@Paul Newcome I can always count on you!

So I used this formula on this thread from you to try to help me rank my projects. The issue is some of our projects are forced into a ranking of 1 no matter how high (or low) they score. So we have a handful of projects with a ranking of 1, then subsequent projects with rankings of 2,3,4,5, etc etc. I want to use another formula that takes all of my 1's and ranks them in order 1,2,3,4,5… then the formula will take all the highest ranking projects after that and rank them subsequently 6,7,8,9,10… your formula came close but i am missing something. Can you help me?

I used your formulas:

auto column = auto column, nothing fancy

row column =MATCH(auto@row, auto:auto, 0)

Pauls test (RankEQ) = VALUE(RANKEQ(Ranking@row, Ranking:Ranking) + "." + COUNTIFS(Ranking:Ranking, @cell = Ranking@row, row:row, @cell <= row@row))

Ranking column - standard RANKEQ formula with some if conditions

Gia Thinh

Hi @Matthew Campbell,

Is the sorting in the Ascending Sort column is what you want ? If so, you can try the following column formula :

=COUNTIF(Ranking:Ranking, <Ranking@row) + COUNTIFS(row:row, <row@row, Ranking:Ranking, =Ranking@row) + 1

Hope this works for you!

Matthew Campbell

@Gia Thinh - you are the man! It works like a charm. that would of probably taken me 10 hours… thank you!

Gia Thinh

You're welcome. Great to hear it worked for you!