Rank by Year/Client/Count when lines are duplicated

[janetk411]

[TABLE="width: 500"]
<tbody>[TR]
[TD]YEAR
[/TD]
[TD]YRFILECNT
[/TD]
[TD]YEARRANK
[/TD]
[TD]CLIENT
[/TD]
[TD]BR
[/TD]
[TD]STA
[/TD]
[TD]STACNT
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]342
[/TD]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]32
[/TD]
[TD]CHIO
[/TD]
[TD]207
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]34
[/TD]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]134
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]342
[/TD]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]42
[/TD]
[TD]LAX
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]267
[/TD]
[TD]2
[/TD]
[TD]DEF
[/TD]
[TD]52
[/TD]
[TD]SFO
[/TD]
[TD]250
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]267
[/TD]
[TD]2
[/TD]
[TD]DEF
[/TD]
[TD]52
[/TD]
[TD]SFA
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]267
[/TD]
[TD]2
[/TD]
[TD]DEF
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]223
[/TD]
[TD]3
[/TD]
[TD]GHI
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]222
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]223
[/TD]
[TD]3
[/TD]
[TD]GHI
[/TD]
[TD]32
[/TD]
[TD]CHIO
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]72
[/TD]
[TD]4
[/TD]
[TD]JKL
[/TD]
[TD]32
[/TD]
[TD]CHIO
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]72
[/TD]
[TD]4
[/TD]
[TD]JKL
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]72
[/TD]
[TD]5
[/TD]
[TD]MNO
[/TD]
[TD]32
[/TD]
[TD]CHIO
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]72
[/TD]
[TD]5
[/TD]
[TD]MNO
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]28
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]201
[/TD]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]32
[/TD]
[TD]CHIO
[/TD]
[TD]183
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]201
[/TD]
[TD]1
[/TD]
[TD]ABC
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]179
[/TD]
[TD]2
[/TD]
[TD]DEF
[/TD]
[TD]32
[/TD]
[TD]CHIA
[/TD]
[TD]179
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]95
[/TD]
[TD]3
[/TD]
[TD]GHI
[/TD]
[TD]7
[/TD]
[TD]DTT
[/TD]
[TD]95
[/TD]
[/TR]
</tbody>[/TABLE]
The table above represents the number of files (B - YRFILECNT), per client (D), per year (A).
I need to rank the YRFILECNT as shown in column C. So client ABC is ranked #1 2019. Client DEF is #2 with the second highest rank.
Clients JKL and MNO had the same count in 2019 but I want a unique rank assigned to each client.
Can someone help me with this formula? Thank you in advance.

 

[dnorm]

If this is a table, could you not do it using a custom sort (found in the filter tab)?
sort each value in the method, adding a new level for each.

 

[janetk411]

dnorm,
Sorry, I am not clear on what you are suggesting ... How would that accomplish populating the "rank" values in the column?

 

[dnorm]

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 496"]
<tbody>[TR]
[TD="width: 81"]YEAR

[/TD]
[TD="width: 127"]YRFILECNT
[/TD]
[TD="width: 124"]YEARRANK
[/TD]
[TD="width: 96"]CLIENT
[/TD]
[TD="width: 64"]BR
[/TD]
[TD="width: 69"]STA
[/TD]
[TD="width: 100"]STACNT
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]342
[/TD]
[TD="width: 124, bgcolor: white"]1
[/TD]
[TD="width: 96, bgcolor: white"]ABC
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIO
[/TD]
[TD="width: 100"]207
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]342
[/TD]
[TD="width: 124, bgcolor: white"]1
[/TD]
[TD="width: 96, bgcolor: white"]ABC
[/TD]
[TD="width: 64, bgcolor: white"]42
[/TD]
[TD="width: 69, bgcolor: white"]LAX
[/TD]
[TD="width: 100"]1
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]267
[/TD]
[TD="width: 124, bgcolor: white"]2
[/TD]
[TD="width: 96, bgcolor: white"]DEF
[/TD]
[TD="width: 64, bgcolor: white"]52
[/TD]
[TD="width: 69, bgcolor: white"]SFO
[/TD]
[TD="width: 100"]250
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]267
[/TD]
[TD="width: 124, bgcolor: white"]2
[/TD]
[TD="width: 96, bgcolor: white"]DEF
[/TD]
[TD="width: 64, bgcolor: white"]52
[/TD]
[TD="width: 69, bgcolor: white"]SFA
[/TD]
[TD="width: 100"]16
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]267
[/TD]
[TD="width: 124, bgcolor: white"]2
[/TD]
[TD="width: 96, bgcolor: white"]DEF
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]1
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]223
[/TD]
[TD="width: 124, bgcolor: white"]3
[/TD]
[TD="width: 96, bgcolor: white"]GHI
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]222
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]223
[/TD]
[TD="width: 124, bgcolor: white"]3
[/TD]
[TD="width: 96, bgcolor: white"]GHI
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIO
[/TD]
[TD="width: 100"]1
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]72
[/TD]
[TD="width: 124, bgcolor: white"]4
[/TD]
[TD="width: 96, bgcolor: white"]JKL
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIO
[/TD]
[TD="width: 100"]44
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]72
[/TD]
[TD="width: 124, bgcolor: white"]4
[/TD]
[TD="width: 96, bgcolor: white"]JKL
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]28
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]72
[/TD]
[TD="width: 124, bgcolor: white"]5
[/TD]
[TD="width: 96, bgcolor: white"]MNO
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIO
[/TD]
[TD="width: 100"]44
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]72
[/TD]
[TD="width: 124, bgcolor: white"]5
[/TD]
[TD="width: 96, bgcolor: white"]MNO
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]28
[/TD]
[/TR]
[TR]
[TD="width: 81"]19
[/TD]
[TD="width: 127, bgcolor: white"]34
[/TD]
[TD="width: 124, bgcolor: white"]1
[/TD]
[TD="width: 96, bgcolor: white"]ABC
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]134
[/TD]
[/TR]
[TR]
[TD="width: 81"]18
[/TD]
[TD="width: 127, bgcolor: white"]201
[/TD]
[TD="width: 124, bgcolor: white"]1
[/TD]
[TD="width: 96, bgcolor: white"]ABC
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIO
[/TD]
[TD="width: 100"]183
[/TD]
[/TR]
[TR]
[TD="width: 81"]18
[/TD]
[TD="width: 127, bgcolor: white"]201
[/TD]
[TD="width: 124, bgcolor: white"]1
[/TD]
[TD="width: 96, bgcolor: white"]ABC
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]18
[/TD]
[/TR]
[TR]
[TD="width: 81"]18
[/TD]
[TD="width: 127, bgcolor: white"]179
[/TD]
[TD="width: 124, bgcolor: white"]2
[/TD]
[TD="width: 96, bgcolor: white"]DEF
[/TD]
[TD="width: 64, bgcolor: white"]32
[/TD]
[TD="width: 69, bgcolor: white"]CHIA
[/TD]
[TD="width: 100"]179
[/TD]
[/TR]
[TR]
[TD="width: 81"]18
[/TD]
[TD="width: 127"]95
[/TD]
[TD="width: 124"]3
[/TD]
[TD="width: 96"]GHI
[/TD]
[TD="width: 64"]7
[/TD]
[TD="width: 69"]DTT
[/TD]
[TD="width: 100"]95
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Are you trying to achieve something like this?

 

[janetk411]

yes That is what I need to do

 

[dnorm]

this was done using a layered sort filter.
unfortunately I cannot add pictures but;

On the ribbon, in the Sort &Filter dropdown options. Select the custom sort and add levels of sort.

Filter by:

YEAR - Largest to Smallest
YRFILECNT - Largest to Smallest
CLIENT - A to Z