Finding Rows with Similar Data (based on Date, Time, Numeric Values)

M

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
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>
 
Sort by date Sort by votes
mlarson said:
Is there a way to get the same results without using an array? My Excel spreadsheet times out before completing (about 75,000 rows in my actual spreadsheet). Perhaps adding additional columns with additional formulas to make it work without using an array? Thanks for your help.
Click to expand...

OK, a 2-column approach.

AB1: =MIN(AB2:AB1048576)
AB2: =IF(AND(-1<=X2,X2<0),ROWS(AB$2:AB2))
AB3: =IF(AND(B3>N(INDEX(B$1:B2,MAX(AB$2:AB2)+1)),-1<=X3,X3<0),ROWS(AB$2:AB3))

AA2: =IF(AND(ROWS(AC$2:AC2)>=AB$1,OR(COUNT(AB2),H2=INDEX(S$2:S2,MAX(AB$2:AB2)),H2=INDEX(Z$2:Z2,MAX(AB$2:AB2)))),"Keep","")

Fill AA2 down into AA3, then select AA3:AB3 and fill down as far as needed.

Or a 3-column approach (should be fastest recalculating). AB2 and AB3 formulas as above.

AC2: =IF(AB2,AB2)
AC3: =IF(AB3,AB3,AC2)

AA2: =IF(AC2,"Keep","")
AA3: =IF(IF(AC3,IF(N(AC3)>N(AC2),1,OR(H3=INDEX(S$2:S2,AC3),H3=INDEX(Z$2:Z2,AC3)))),"Keep","")

Select AA3:AC3 and fill down as far as needed.
 
Last edited:
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You must log in or register to reply here.

Similar threads

W
  • Question
Sort data with calculated fields gives unexpected results
Replies
4
Views
390
wpryan
W
F
  • Question
Time Percentages
Replies
1
Views
800
James006
James006
R
  • Question
Indirect formula with conditions
Replies
2
Views
690
richard1234567891011
R
G
  • Question
copy/paster to another worksheet
Replies
5
Views
661
Fluff
Fluff
D
  • Question
Sales from days of this month vs same quantity of days same month last year.
Replies
2
Views
1K
Daniboy
D

Forum statistics

Threads
1,216,075
Messages
6,128,662
Members
449,462
Latest member
Chislobog

Share this page

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