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
Bookmarks