Creating a dynamic descending list with duplicate values

[jwills7]

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

 

[JamesCanale]

Maybe SORT function in 365?

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend
2	23	100	80	Closed	20
3	42	200	150	Active	10
4	56	100	90	Active	30
5	71	150	30	Active	40
6	83	100	85	Active	40
7
8	Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend
9	42	200	150	Active	10
10	71	150	30	Active	40
11	23	100	80	Closed	20
12	56	100	90	Active	30
13	83	100	85	Active	40
Sheet26

Cell Formulas
Range	Formula
A9:E13	A9	=SORT(A2:E6,2,-1)
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend					Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend
2	23	100	80	Closed	20					42	200	150	Active	10
3	42	200	150	Active	10					71	150	30	Active	40
4	56	100	90	Active	30					56	100	90	Active	30
5	71	150	30	Active	40					83	100	85	Active	40
6	83	100	85	Active	40
7
Data

Cell Formulas
Range	Formula
J2:N5	J2	=SORT(FILTER(A2:E6,D2:D6<>"closed"),2,-1)
Dynamic array formulas.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating a dynamic descending list with duplicate values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jwills7]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend					Job #	Job Revenue	Job Cost	Job Status	Job Mat Spend
2	23	100	80	Closed	20					42	200	150	Active	10
3	42	200	150	Active	10					71	150	30	Active	40
4	56	100	90	Active	30					56	100	90	Active	30
5	71	150	30	Active	40					83	100	85	Active	40
6	83	100	85	Active	40
7
Data

Cell Formulas
Range	Formula
J2:N5	J2	=SORT(FILTER(A2:E6,D2:D6<>"closed"),2,-1)
Dynamic array formulas.

the problem here is that I would only like to pull in column A from the first list instead of all of the data from the first table.

 

[Fluff]

In that case how about

=INDEX(SORT(FILTER(A2:B6,D2:D6<>"closed"),2,-1),,1)