Creating a dynamic descending list with duplicate values

Any help would be tremendously appreciated. I am on hour 2 of trying to figure this out.

An example:

Tab 1:

Job # Job Revenue Job Cost Job Status Job Mat Spend
23 / 100 / 80 / Closed / 20
42 / 200 / 150 / Active / 10
56 / 100 / 90 / Active / 30
71 / 150 / 30 / Active / 40
83 / 100 / 85 / Active / 40
I have around 5,000 rows of this type of information.

I would like to create a dynamic list in descending order (of Job Revenue) of each of these jobs with their respective job information. The issue I am having is when I create a list like this using a combination of index match large if, ect. The duplicate values will return the first occurrence of that value. I also need to exclude any closed jobs.

What I am getting now with my "INDEX(Pivot_Table[Job_No.],MATCH(LARGE(IF(Pivot_Table[job_status]="A",Pivot_Table[Job_Revenue]),ROWS('Sheet 1'!$B$2:$B2)),Pivot_Table[Job_Revenue],0),0)" function:



Job # Job Revenue Job Cost Job Status Job Mat Spend
42 / 200 / 150 / Active / 10
71 / 150 / 30 / Active / 40
56 / 100 / 90 / Active / 30
56 / 100 / 90 / Active / 30


What I am looking for:



Job # Job Revenue Job Cost Job Status Job Mat Spend
42 / 200 / 150 / Active / 10
71 / 150 / 30 / Active / 40
56 / 100 / 90 / Active / 30
83 / 100 / 85 / Active / 40

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I will do it for you today: https://www.mrexcel.com/board/thread...alues.1181404/.)