Sort data list based on formula with interchanging signs (<,>,<=,>=) within the formula

[rhogsett]

Hello Everyone,

I am working with a large data set and and have a formula for generating a list (starting in D14) based on the highest value of Backlog $ within the set parameters starting in G14. The issue is that the parameters, largely the signs (<,>,<=,>=), are part of the formula, ie. {=IFERROR(LARGE(IF($B$3:$B$9>$I$14,IF($D$3:$D$9<=$I$16,IF($C$3:$C$9>=$I$15,$C$3:$C$3))),ROW(A1)),"-")}
I am hoping there is a way to have the signs in the formula to be replaced by cell references (H16,H17,& H18) so that any sign can be selected to adjust the desired output. Any suggestions on how to achieve this?

	A	B	C	D	E	F	G	H	I
1	DATA
2	Project	Creation Date	Backlog $	% Complete
3	A	1/15/2017	$1,000	90%
4	B	3/15/2018	$10,000	75%
5	C	4/1/2016	$-	100%
6	D	5/15/2018	$5,000	10%
7	E	2/3/2017	$3,000	85%
8	F	12/25/2017	$15,000	75%
9	G	10/31/2016	$1,000	98%
10
11
12
13	OUTPUT	Project	Creation Date	Backlog $	% Complete		SEARCH PARAMETERS
14	1	F	12/25/2017	$15,000	75%		Creation Date	>	1/1/2017
15	2	B	3/15/2018	$10,000	75%		Backlog $	>	$2,000.00
16	3	D	5/15/2018	$5,000	10%		% Complete	<	100%
17	4	E	2/3/2017	$3,000	85%
18	5	-	-	-	-
19	6	-	-	-	-
20	7	-	-	-	-

<tbody>
</tbody>

Thank you in advance for your help!

RHOGSETT

 

[Worf]

Hello

' sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [h17:h19]) Is Nothing Then
    [d14:d20].FormulaArray = _
    "=IFERROR(LARGE(IF($B$3:$B$9" & [h17] & "$I$14,IF($D$3:$D$9" & [h18] & _
    "=$I$16,IF($C$3:$C$9" & [h19] & "=$I$15,$C$3:$C$3))),ROW(A1)),"" - "")"
End If
End Sub

 

[Marcelo Branco]

Hello Everyone,

I am working with a large data set and and have a formula for generating a list (starting in D14) based on the highest value of Backlog $ within the set parameters starting in G14. The issue is that the parameters, largely the signs (<,>,<=,>=), are part of the formula, ie. {=IFERROR(LARGE(IF($B$3:$B$9>$I$14,IF($D$3:$D$9<=$I$16,IF($C$3:$C$9>=$I$15,$C$3:$C$3))),ROW(A1)),"-")}
I am hoping there is a way to have the signs in the formula to be replaced by cell references (H16,H17,& H18) so that any sign can be selected to adjust the desired output. Any suggestions on how to achieve this?

It's possible to achieve this, but with a large data set the performance of the spreadsheet would be poor. I think a macro is the proper solution.

Using formulas - something like

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	DATA​
2​	Project​	Creation Date​	Backlog $​	% Complete​
3​	A​	01/15/2017​	1000​	90%​
4​	B​	03/15/2018​	10000​	75%​
5​	C​	04/01/2016​	-​	100%​
6​	D​	05/15/2018​	5000​	10%​
7​	E​	02/03/2017​	3000​	85%​
8​	F​	12/25/2017​	15000​	75%​
9​	G​	10/31/2016​	1000​	98%​
10​
11​
12​
13​	OUTPUT​	Project​	Creation Date​	Backlog $​	% Complete​		SEARCH PARAMETERS​
14​	1​	F​	12/25/2017​	15000​	75%​		Creation Date​	>​	01/01/2017​
15​	2​	B​	03/15/2018​	10000​	75%​		Backlog $​	>​	2000​
16​	3​	D​	05/15/2018​	5000​	10%​		% Complete​	<​	100%​
17​	4​	E​	02/03/2017​	3000​	85%​
18​	5​	-​	-​	-​	-​
19​	6​	-​	-​	-​	-​
20​	7​	-​	-​	-​	-​

<tbody>
</tbody>

Array formula in D14 copied down
=IFERROR(LARGE(IF(COUNTIFS(B$3:B$9,B$3:B$9,B$3:B$9,H$14&I$14),IF(COUNTIFS(C$3:C$9,C$3:C$9,C$3:C$9,H$15&I$15),IF(COUNTIFS(D$3:D$9,D$3:D$9,D$3:D$9,H$16&I$16),C$3:C$9))),A14),"-")
Ctrl+Shift+Enter

Array formula in B14 copied down
=IF(ISNUMBER(D14),INDEX(A$3:A$9,SMALL(IF(C$3:C$9=D14,ROW(C$3:C$9)-ROW(C$3)+1),COUNTIF(D$14:D14,D14))),"-")
Ctrl+Shift+Enter

Then you can use VLOOKUP to get the Creation Date and % Complete of each project.

M.

 

[rhogsett]

Marcelo Branco, this worked perfectly, thank you!

 

[Marcelo Branco]

Marcelo Branco, this worked perfectly, thank you!

You are welcome. Thanks for the feedback.

M.