return the nth occurence

[AFST]

Hey all

I have a variety of dates in cells A1:A100. I want a formula in cell B1 that checks the A column for a date that is within 30 days of today (TODAY()-30) and returns the first occurrence that meets the criteria. Similarly in cell B2 i want it to do the same evaluation except return the second occurrence of a date within 30 days. Ultimately I'll do this 5 times, and if its all done correctly as you move down column B it should go from the oldest date within 30 days to the most recent. Can anyone lend a hand?

 

[Matty]

Are your dates sorted? Or random? And should the oldest or newest date be returned first?

Matty

 

[AFST]

Still working on the details for that actually but they should eventually be sorted yes.

 

[ChrisOswald]

try this array formula: (copy to b1, enter with control-shift-enter, paste down)

=INDIRECT("A" & SMALL(IF(TODAY()-A$1:A$100<=30,ROW(A$1:A$100),999999999),ROW()))

if your dates end up starting in a different row, change the final row() in the formula to be row() -([starting row] - 1)

Edit: Sorry, I didn't read at first that you wanted them returned by the oldest valid date: (the original formula returned by their order in the list). Try this array formula instead:

=SMALL(IF(TODAY()-A$1:A$100<=30,A$1:A$100,99999999),ROW())

Note: this formula will return duplicated dates: in my sample data I had it return 7/11, 7/11, 7/12, 7/13, 7/14 (to instances of 7/11 in a1:a100)

 

[Matty]

Perhaps something like:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:124px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Dates</td><td>Dates >=Today - 30</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">01/07/2011</td><td style="text-align:right; ">11/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">05/07/2011</td><td style="text-align:right; ">12/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">06/07/2011</td><td style="text-align:right; ">13/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">07/07/2011</td><td style="text-align:right; ">14/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:right; ">08/07/2011</td><td style="text-align:right; ">15/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">09/07/2011</td><td style="text-align:right; ">16/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; ">10/07/2011</td><td style="text-align:right; ">20/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:right; ">11/07/2011</td><td style="text-align:right; ">21/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:right; ">12/07/2011</td><td style="text-align:right; ">22/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:right; ">13/07/2011</td><td style="text-align:right; ">23/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:right; ">14/07/2011</td><td style="text-align:right; ">24/07/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="text-align:right; ">15/07/2011</td><td style="text-align:right; ">10/08/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:right; ">16/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:right; ">20/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="text-align:right; ">21/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:right; ">22/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:right; ">23/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="text-align:right; ">24/07/2011</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="text-align:right; ">10/08/2011</td><td>
</td></tr></tbody></table>
Formula in B2 is:

=IF(ROWS(B$2:B2)<=COUNTIF(A$2:A$20,">="&TODAY()-30),INDEX(A$2:A$20,SMALL(IF(A$2:A$20>=TODAY()-30,ROW(A$2:A$20)-ROW(A$2)+1),ROWS(B$2:B2))),"")

Is this something you could work with?

Matty

 

[AFST]

It actually starts in row 35. And another small problem (hopefully small anyway) is column A is actually a merged cell from A:G. I'm way to far along now to change this merged thing into a single column. Is there a way around this?

Just another small point instead of the first and second... occurrences turning up in B1 B2 .... its actually B1 C1 ....

New advice?

 

[AFST]

Your example is really close by the looks of it, I just need it to start at row 35 and I only need to show the first 5 occurrences but across a set of columns not down a range

 

[AFST]

Chris,

It looks like the second formula you provided does exactly what I want it do (I ran in it a little sample). But now for the real thing is there a way to get around the fact that my the dates are in merged columns, and the resultant cell itself is a merged cell. And how do I continue the formula across 5 columns instead of down 5 rows???

 

[ChrisOswald]

The merge cells shouldn't be a problem. (I'm not certain... there's enough excel experts on this board that I've learned from that absolutely hate merged cells, so I've barely used them in years).

So, going across, the formula in B1 should be

=SMALL(IF(TODAY()-$A$35:$A$134<=30,$A$35:$A$134,99999999),COLUMN()-1)

In general, the $A$35:$A$134 can be any range that has your dates of interest, the <=30 bit says which items you care to look at, and the Column()-1 is the part of the small function that returns: in column b, column() = 2, so it returns 1 (and small() returns the smallest item in the range), in column C, column () = 3, so it returns 2 (and small() returns the 2nd smallest), etc

99999999 is just an arbitrarily large number that won't get picked up by small as being a small number.

 

[AFST]

Alright that new formula works perfectly in my sample and produces exactly what I want it to, but I still can't get it to work in the real thing.

If I just press enter in returns the #VALUE! error and if I try the Ctrl+Shift+Enter method its says "Array formulas are not valid in merged cells"

I'm so close to the solution! I would like to go back and make everything unmerged but it just isn't really an option because I'm too far into the project.

Is it because the resultant cell is merged?

I tried unmerging the resultant cell and it will let me enter the formula as an arry but a new problem has come up. It returns a bunch of ####### signs saying that if its a negative number or extremely large that's what it will do. Does that have anything to do with the fact that my resultant cell is located at O21?? Again looking to avoid the unmerge and this new problem. Sorry I keep bugging you about this one.