Sorting items based on rank and varied pick

Dear community,

I would like to know what would be the formula for sorting names based on two factors 1) rank 2) varied pick.

So you can imagine that I have 13 names that are ranked from 1 being the best and 13 being the worst. Each day there is a specific pick where specific amount of names must be in sorted to the top, but the ones that were not picked, should be in the bottom, despite their rank.
It is hard to explain by words, therefore please see my example here:
Example.xlsx
Screenshot 2022-10-28 105550.png

Please update your profile with the version of Excel you are using an your location (country is close enough).

And . . . Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

Please add MS365 to your forum profile. Thanks.

This would work if you added a 0 in front of the single digits:

=SORTBY(H21:H33,G21:G33,-1,H21:H33,1)

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	G	H	I	J	K	L	M
20	Day 1 Pick	Player based on rank			Sorted items based on rank and pick
21	x	01. Jokic			1. Jokic		01. Jokic
22		02. Irving			3. Murray		03. Murray
23	x	03. Murray			5. Shai		05. Shai
24		04. VanVleet			8. Harris		08. Harris
25	x	05. Shai			13. Wiggins		13. Wiggins
26		06. Brown			2. Irving		02. Irving
27		07. Collins			4. VanVleet		04. VanVleet
28	x	08. Harris			6. Brown		06. Brown
29		09. Banchero			7. Collins		07. Collins
30		10. Johnson			9. Banchero		09. Banchero
31		11. Russel			10. Johnson		10. Johnson
32		12. Wood			11. Russel		11. Russel
33	x	13. Wiggins			12. Wood		12. Wood

Sheet: Sheet1

Thanks AliGW. I am new to this forum, sorry if I do something wrong.

Have a read of the forum rules and the FAQ - links at the top - it's all there.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Signs of a misspent retirement ...