Storing unused values in array(?) for later use in formula

[aiki100]

Hello all -
I have a question, please:

Col A has values.
Col B checks a condition and returns true or not ("Y").
If Col B returns TRUE, Col C stores Col A's value.
Col D repeats the stored value so that:

Col E can check Col D against a different condition, and returns TRUE or not.

This contuinues until:

Col E returns TRUE ("condition met"), or
Col B (checking original condition) returns TRUE ("Y").
If Col B finds a new value has satisified the original condition, it stores that new value in Col C (and thus in D as well).

My problem is what to do with situations where Col E does not return true ("condition met") before a new value is stored in Col C and Col D.

That "unused," value is still relevant, and may satisfy the condition in the future, so I need a way to keep track of these "unused," values and have Col E check both the current row against the running value in Col C (the regular operation), as well as against all other "unused," values from Col D above. In this example there is only one value (101.78) that has been "unused," but with more rows, there could be many "unused" values over time.

I have tried a number of ways to accomplish this, but none work. Is it possible to do this sort of thing, please? If so, any help will be very much appreciated, thank you!

The FO Q copy.xlsx
	A	B	C	D	E
1	Value	Satisfies condition	Value that satisfied	Value Running	Different Condition
2
3	82.00			0.00
4	99.83	Y	99.83	99.83
5	99.12			99.83
6	99.00			99.83
7	99.60			99.83
8	101.51			99.83	condition met
9	101.78	Y	101.78	101.78
10	100.37			101.78
11	99.68			101.78
12	101.00			101.78
13	91.00			101.78
14	84.00			101.78
15	85.00	Y	85.00	85.00
16	84.90			85.00
17	86.00			85.00	condition met
18	86.26			85.00
Sheet1

Cell Formulas
Range	Formula
C3:C18	C3	=IF(B3="Y",A3,"")
D3:D18	D3	=IF(C3="",D2,C3)
E3:E18	E3	=IF(AND(A2<D3,A3>D3), "condition met","")

 

[aiki100]

Greetings -
To all who looked at this post, thanks for checking it out.

Since I posted, I have (of course), kept slogging on a solution to this myself, and although I am sure there must be a better way, I thought I'd post my current (partial) solution to my original post, in case it helps someone else.

To recap - the question was:

1 - How to identify and store unused values in a list for later use,
2 - Test against that list in addition to the original tests,
3 - Discard values in the list once they are used,
4 - Reset that unused value list every x rows.

This solution solves #1. (Yes, I know, I am slow, lol...)
I have yet to figure out how to integrate col H values into the "different condition" formula in col E, so that it tests the way it does now, and ALSO tests against col H's unique unused values. Once I figure out how to combine these, I'll need to find out whether col H will function properly if the table is filtered.

I hope what I've done so far will be of use to someone else, and of course, if anyone has ideas on how to finish up the probelm (or do it all better) that would be great, too!

Store values Question.xlsx
	A	B	C	D	E	F	G	H
1	Value	Satisfies condition	Value that satisfied	Satisfied Value Running	Different Condition	Condition Met Values	Unmet Values	Unique Unused
2
3	82.00			0.00				101.51
4	99.83	Y	99.83	99.83		99.83		92.00
5	99.12			99.83		99.83		93.00
6	99.99			99.83	condition met	0.00
7	99.60			99.83
8	101.51	Y	101.51	101.51		101.51
9	101.78			101.51		101.51
10	91.00			101.51		101.51
11	84.00			101.51		101.51	101.51
12	85.00	Y	85.00	85.00		85.00
13	84.90			85.00		85.00
14	86.00			85.00	condition met	0.00
15	86.26			85.00
16	90.00			85.00
17	88.00			85.00
18	92.00	Y	92.00	92.00		92.00
19	92.00			92.00		92.00
20	98.00			92.00		92.00
21	100.00			92.00		92.00
22	90.00			92.00		92.00	92.00
23	93.00	Y	93.00	93.00		93.00
24	91.00			93.00		93.00	93.00
FINAL Less rows

Cell Formulas
Range	Formula
G2:G24	G2	=IF(AND(F2=F1,F2<>0,F2<>D3),F2,"")
C3:C24	C3	=IF(B3="Y",A3,"")
D3:D24	D3	=IF(C3="",D2,C3)
E3	E3	=IF(AND(A2<D3,A3>D3), "Y","")
F3:F24	F3	=IF(C3<>"",C3, IF(E3="condition met",0, IF(F2=0,"", IF(E3="",F2,""))))
H3:H24	H3	=IFERROR(INDEX($G$2:$G$24, MATCH(0,INDEX(COUNTIF($H$2:H2, $G$2:$G$24),0,0),0)),"")
E4:E24	E4	=IF(AND(A3<D4,A4>D4), "condition met","")