Lucydeford welcome to the forum.
Since you apparently have large data
I did this using the "Custom List" inside Sort but my list of employees is too long.
I would recommend a helper column to avoid resource heavy array formula.
The helper formula in C2 and filled down ranks the years and breaks "ties".
Formula:
=COUNTIF($A$2:$A$1001,"<="&A2)+ROWS($2:2)/10^5
Then in E2 filled down and across until you get blanks.
Formula:
=IFERROR(INDEX(A$2:A$1001,MATCH(SMALL($C$2:$C$1001,ROWS($2:2)),$C$2:$C$1001,0)),"")
I also "future proofed" the data by increasing the ranges beyond the actual data range.
|
A |
B |
C |
D |
E |
F |
1 |
Years |
Name |
Helper |
|
Rank by Yr |
Name |
2 |
1 |
Alice |
1.00001 |
|
1 |
Alice |
3 |
12 |
Nancy |
6.00002 |
|
2 |
Jane |
4 |
2 |
Jane |
2.00003 |
|
3.5 |
Joe |
5 |
3.5 |
Joe |
3.00004 |
|
4 |
Bob |
6 |
4 |
Bob |
4.00005 |
|
5 |
Nick |
7 |
5 |
Nick |
5.00006 |
|
12 |
Nancy |
Bookmarks