Rank by Year/Client/Count when lines are duplicated

[janetk411]

YEAR	YRFILECNT	YEARRANK	CLIENT	BR	STA	STACNT
19	342	1	ABC	32	CHIO	207
19	34	1	ABC	32	CHIA	134
19	342	1	ABC	42	LAX	1
19	267	2	DEF	52	SFO	250
19	267	2	DEF	52	SFA	16
19	267	2	DEF	32	CHIA	1
19	223	3	GHI	32	CHIA	222
19	223	3	GHI	32	CHIO	1
19	72	4	JKL	32	CHIO	44
19	72	4	JKL	32	CHIA	28
19	72	5	MNO	32	CHIO	44
19	72	5	MNO	32	CHIA	28
18	201	1	ABC	32	CHIO	183
18	201	1	ABC	32	CHIA	18
18	179	2	DEF	32	CHIA	179
18	95	3	GHI	7	DTT	95

<tbody>
</tbody>

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]

YEAR YRFILECNT YEARRANK CLIENT BR STA STACNT 19 342 1 ABC 32 CHIO 207 19 342 1 ABC 42 LAX 1 19 267 2 DEF 52 SFO 250 19 267 2 DEF 52 SFA 16 19 267 2 DEF 32 CHIA 1 19 223 3 GHI 32 CHIA 222 19 223 3 GHI 32 CHIO 1 19 72 4 JKL 32 CHIO 44 19 72 4 JKL 32 CHIA 28 19 72 5 MNO 32 CHIO 44 19 72 5 MNO 32 CHIA 28 19 34 1 ABC 32 CHIA 134 18 201 1 ABC 32 CHIO 183 18 201 1 ABC 32 CHIA 18 18 179 2 DEF 32 CHIA 179 18 95 3 GHI 7 DTT 95 <tbody> </tbody>

<tbody>
</tbody>

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