Hi all, I have a problem I hope someone can help me with. I am looking for a formula or macro that can be placed in Column AA.
In Column AA I would like to have "Keep" in any row that is the first row of the day that has a value in Column X that is >= -1.00 and <0.00 (we'll call this First Kept Row Of Day). I would also like "Keep" placed in Column AA for any rows that have the same date as First Kept Row of Day and in which Column H is identical to either Column S or Column Z of the First Kept Row Of Day.
For example, in the spreadsheet below, Row 7 shows "Keep" because it is the first row on date 1/3/2011 (Column B) that has a value >= -1.00 and <0.00 in Column X (Column X shows -0.31). Rows 8, 11, and 12 show "Keep" because they have the same date as Row 7 (1/3/2011), and each has a value in Column H that matches the value of either Column S (600) or Column Z (610) of Row 7.
What I'm looking for is a formula/macro that will show the "Keep" output in Column AA.
Thanks for your help!
<table border="0" cellpadding="0" cellspacing="0" width="1728"><col style="width: 48pt;" width="64" span="27"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 48pt;" width="64">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> <td class="xl63" style="width: 48pt;" width="64">G</td> <td class="xl63" style="width: 48pt;" width="64">H</td> <td class="xl63" style="width: 48pt;" width="64">I</td> <td class="xl63" style="width: 48pt;" width="64">J</td> <td class="xl63" style="width: 48pt;" width="64">K</td> <td class="xl63" style="width: 48pt;" width="64">L</td> <td class="xl63" style="width: 48pt;" width="64">M</td> <td class="xl63" style="width: 48pt;" width="64">N</td> <td class="xl63" style="width: 48pt;" width="64">O</td> <td class="xl63" style="width: 48pt;" width="64">P</td> <td class="xl63" style="width: 48pt;" width="64">Q</td> <td class="xl63" style="width: 48pt;" width="64">R</td> <td class="xl63" style="width: 48pt;" width="64">S</td> <td class="xl63" style="width: 48pt;" width="64">T</td> <td class="xl63" style="width: 48pt;" width="64">U</td> <td class="xl63" style="width: 48pt;" width="64">V</td> <td class="xl63" style="width: 48pt;" width="64">W</td> <td class="xl63" style="width: 48pt;" width="64">X</td> <td class="xl63" style="width: 48pt;" width="64">Y</td> <td class="xl63" style="width: 48pt;" width="64">Z</td> <td class="xl63" style="width: 48pt;" width="64">AA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">10.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">0.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">9.69</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-0.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">10.39</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">11</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">0.39</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">12</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-9.61</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">13</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-19.61</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">14</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">9.25</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">15</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-0.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">16</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">17</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">18</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">6.87</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">19</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-3.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">20</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-13.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">21</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-23.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">22</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">11.23</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">23</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">1.23</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">24</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-8.77</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">25</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-18.77</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> </tbody></table>
In Column AA I would like to have "Keep" in any row that is the first row of the day that has a value in Column X that is >= -1.00 and <0.00 (we'll call this First Kept Row Of Day). I would also like "Keep" placed in Column AA for any rows that have the same date as First Kept Row of Day and in which Column H is identical to either Column S or Column Z of the First Kept Row Of Day.
For example, in the spreadsheet below, Row 7 shows "Keep" because it is the first row on date 1/3/2011 (Column B) that has a value >= -1.00 and <0.00 in Column X (Column X shows -0.31). Rows 8, 11, and 12 show "Keep" because they have the same date as Row 7 (1/3/2011), and each has a value in Column H that matches the value of either Column S (600) or Column Z (610) of Row 7.
What I'm looking for is a formula/macro that will show the "Keep" output in Column AA.
Thanks for your help!
<table border="0" cellpadding="0" cellspacing="0" width="1728"><col style="width: 48pt;" width="64" span="27"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 48pt;" width="64" height="20">A</td> <td class="xl63" style="width: 48pt;" width="64">B</td> <td class="xl63" style="width: 48pt;" width="64">C</td> <td class="xl63" style="width: 48pt;" width="64">D</td> <td class="xl63" style="width: 48pt;" width="64">E</td> <td class="xl63" style="width: 48pt;" width="64">F</td> <td class="xl63" style="width: 48pt;" width="64">G</td> <td class="xl63" style="width: 48pt;" width="64">H</td> <td class="xl63" style="width: 48pt;" width="64">I</td> <td class="xl63" style="width: 48pt;" width="64">J</td> <td class="xl63" style="width: 48pt;" width="64">K</td> <td class="xl63" style="width: 48pt;" width="64">L</td> <td class="xl63" style="width: 48pt;" width="64">M</td> <td class="xl63" style="width: 48pt;" width="64">N</td> <td class="xl63" style="width: 48pt;" width="64">O</td> <td class="xl63" style="width: 48pt;" width="64">P</td> <td class="xl63" style="width: 48pt;" width="64">Q</td> <td class="xl63" style="width: 48pt;" width="64">R</td> <td class="xl63" style="width: 48pt;" width="64">S</td> <td class="xl63" style="width: 48pt;" width="64">T</td> <td class="xl63" style="width: 48pt;" width="64">U</td> <td class="xl63" style="width: 48pt;" width="64">V</td> <td class="xl63" style="width: 48pt;" width="64">W</td> <td class="xl63" style="width: 48pt;" width="64">X</td> <td class="xl63" style="width: 48pt;" width="64">Y</td> <td class="xl63" style="width: 48pt;" width="64">Z</td> <td class="xl63" style="width: 48pt;" width="64">AA</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">2</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">10.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">3</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">0.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">4</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">5</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.00</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.00</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">6</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">9.69</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">7</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-0.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">8</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">9</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.69</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.31</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">10</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">10.39</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">11</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">0.39</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">12</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-9.61</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">13</td> <td class="xl64">1/3/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">600.39</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-19.61</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">14</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">9.25</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">15</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-0.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">16</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-10.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">17</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:31</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">599.25</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-20.75</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">18</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">6.87</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">19</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-3.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">20</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-13.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">21</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:33</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">596.87</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">600</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-23.13</td> <td class="xl67">
</td> <td class="xl66">610</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">22</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">590</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">11.23</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">23</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">600</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">1.23</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">24</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">610</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-8.77</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">KEEP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">25</td> <td class="xl64">1/4/2011</td> <td class="xl63">
</td> <td class="xl65">9:35</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">620</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl63">
</td> <td class="xl68">601.23</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">605</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl68">-18.77</td> <td class="xl67">
</td> <td class="xl66">615</td> <td class="xl66">
</td> </tr> </tbody></table>