Sort and rank data based on dynamic criteria w/ duplicates 1 formula desired instead of 2

Hi avee, welcome to Excel Forum! As no one wants to copy data from a picture, may I suggest you upload the workbook from which it came.
This will speed up the solution process. (Remove any sensitive or extraneous info first.)

To attach a workbook:
Click Edit Post (or just start a new reply.)
Click Go Advanced
Scroll down to Manage Attachments and click.
Now just Browse for your file, then click Upload. Simple!

...Never heard back, so I faked some data. This is defined as a TABLE in the attached workbook, which should take care of your dynamic-range issue.
Paste this in X3 and copy down:

Please Login or Register  to view this content.

(AGGREGATE(14,6 mimics the LARGE function, but can ignore errors, and handle arrays without special entry.)

Paste this in Y3 and copy down:

Please Login or Register  to view this content.

Now copy X3:Y7 and paste in AA3.

Row\Col	B	C	D	E	W	X	Y	Z	AA	AB
1	Score	Name	Program	Status
2	47	Bob2	Mercury	tbd		Active	WCF		Active	Mercury
3	620	Bob3	WCF	Active		732	Bob7		361	Bob16
4	222	Bob4	Mercury	Active		688	Bob17		222	Bob4
5	660	Bob5	WCF	Active		660	Bob5		197	Bob15
6	205	Bob6	Mercury	tbd		658	Bob9		127	Bob20
7	732	Bob7	WCF	Active		658	Bob13		74	Bob12
8	400	Bob8	WCF	tbd
9	658	Bob9	WCF	Active
10	644	Bob10	WCF	Active
11	139	Bob11	Mercury	tbd
12	74	Bob12	Mercury	Active
13	658	Bob13	WCF	Active
14	518	Bob14	WCF	tbd
15	197	Bob15	Mercury	Active
16	361	Bob16	Mercury	Active
17	688	Bob17	WCF	Active
18	554	Bob18	WCF	Active
19	475	Bob19	WCF	Active
20	127	Bob20	Mercury	Active
21	637	Bob21	WCF	Active
22	625	Bob22	WCF	Active

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

Thank you Leelnich!! I appreciate your help and I will remember to add the excel file attachment next time.