Rank sequential numbering and skipping certain criteria

chofacker
chofacker
in Modeling

Hello! 

 

I am looking to create sequential numbering on employee numbered list within their respective cost centers but also looking to skip certain criteria such as TBHs. I am currently using RANK(1, ASCENDING, SEQUENTIAL, TRUE, LIST) which works well for numbering within the cost center but includes all members of the Employee (#) list. I would like to skip or ignore TBHs that are in the list. Below is an example of what I would like to see. 

 

List - Employees (#)

Adam Smith 1

Jane Doe 2

TBH (skip)

Tom Miller 3

 

Any thoughts on how to get the sequential numbering to skip members of the list based on any number of attributes?

 

Thanks in advance!

Tagged:

Best Answer

  • Nagaraju
    Nagaraju
    Answer ✓

    Hi @chofacker 

     

    You have to control the "Include value" in Rank function. Instead of TRUE, create a line item to only include the non-TBH employees and refer it in the formula. See the approach below. Hope it helps!

     

    Nagaraju_0-1648738640461.pngNagaraju_1-1648738664187.png

     

Categories