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

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
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
ABCDE
1ValueSatisfies conditionValue that satisfiedValue RunningDifferent Condition
2
382.00 0.00 
499.83Y99.8399.83 
599.12 99.83 
699.00 99.83 
799.60 99.83 
8101.51 99.83condition met
9101.78Y101.78101.78 
10100.37 101.78 
1199.68 101.78 
12101.00 101.78 
1391.00 101.78 
1484.00 101.78 
1585.00Y85.0085.00 
1684.90 85.00 
1786.00 85.00condition met
1886.26 85.00 
Sheet1
Cell Formulas
RangeFormula
C3:C18C3=IF(B3="Y",A3,"")
D3:D18D3=IF(C3="",D2,C3)
E3:E18E3=IF(AND(A2<D3,A3>D3), "condition met","")
 

Attachments

  • Screen Shot 2022-11-04 at 08.53.09 AM.png
    Screen Shot 2022-11-04 at 08.53.09 AM.png
    254.3 KB · Views: 9

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
ABCDEFGH
1ValueSatisfies conditionValue that satisfiedSatisfied Value RunningDifferent Condition Condition Met ValuesUnmet ValuesUnique Unused
2 
382.00 0.00   101.51
499.83Y99.8399.83 99.83 92.00
599.12 99.83 99.83 93.00
699.99 99.83condition met0.00  
799.60 99.83    
8101.51Y101.51101.51 101.51  
9101.78 101.51 101.51  
1091.00 101.51 101.51  
1184.00 101.51 101.51101.51 
1285.00Y85.0085.00 85.00  
1384.90 85.00 85.00  
1486.00 85.00condition met0.00  
1586.26 85.00    
1690.00 85.00    
1788.00 85.00    
1892.00Y92.0092.00 92.00  
1992.00 92.00 92.00  
2098.00 92.00 92.00  
21100.00 92.00 92.00  
2290.00 92.00 92.0092.00 
2393.00Y93.0093.00 93.00  
2491.00 93.00 93.0093.00 
FINAL Less rows
Cell Formulas
RangeFormula
G2:G24G2=IF(AND(F2=F1,F2<>0,F2<>D3),F2,"")
C3:C24C3=IF(B3="Y",A3,"")
D3:D24D3=IF(C3="",D2,C3)
E3E3=IF(AND(A2<D3,A3>D3), "Y","")
F3:F24F3=IF(C3<>"",C3, IF(E3="condition met",0, IF(F2=0,"", IF(E3="",F2,""))))
H3:H24H3=IFERROR(INDEX($G$2:$G$24, MATCH(0,INDEX(COUNTIF($H$2:H2, $G$2:$G$24),0,0),0)),"")
E4:E24E4=IF(AND(A3<D4,A4>D4), "condition met","")

partial solution.png
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top