Sorting a list by a set ranking

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:

Please Login or Register  to view this content.

Then in E2 filled down and across until you get blanks.

Formula:

Please Login or Register  to view this content.

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

No. The solution I offered will work.

With only 63 names you could use an array formula (with ranges limited to the data range) with no ill effects. That would make the helper column unnecessary. It would also make for a longer formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Array entered in D2 filled down and across.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E
1	Years	Name		Rank by Yr	Name
2	1	Alice		1	Alice
3	12	Nancy		2	Jane
4	2	Jane		3.5	Joe
5	3.5	Joe		4	Bob
6	4	Bob		5	Nick
7	5	Nick		12	Nancy