Excel Macro

The requirement seems simple. However we need to understand what does the action "hold it for 10 days" mean? Is there a date in a cell that you want to update with a date 10 days from today?

Column A (Contains Period): 1, 2, 3, 4....upto 2000

Column B (contains Ratio) : 1.2, 5.2, 2.5, 2.8, 4.7 .....upto 2000 Rows

Column C (Contains 1st_Signal ) :IF(B2>5,"1st_Signal",IF(B2=5,"1st_Signal",""))....upto 2000 Rows

Need Help with
Column D (Contains Notification_Signal):
If there are two signals created in column C within 15 days (Period), than notification generated in Column D at the same row of 2nd signal in Column C. (Notification is only generated in column D if there are two signals in column C with in 15 days. So out of 2000 rows in Column C, there can be signal at 10th row and at 20th row....may be at 200th row etc)


Please Help, I really appreciate any help. Just need help with column D Macro or Formula....
Let me know if you need further clarification Saagar, Help is much appreciated.

I assume the first day data starts in Row2. Insert this formula in Cell D2 and copy paste it in the remaining cells in the column.
=IF(C2="1st_Signal",COUNTIF(OFFSET(C2,-MIN(14,ROW(C2)-2),0,MIN(14,ROW(C2)-2)+1,1),"1st_signal")-1,0)

The function looks at Col C in current tow, if it reads "1st_signal", it returns the number of "1st_signal"s in the preceding 14 rows. If the number is ">0", it meets your condition of multiple occurrences in 15 days.

If your data starts in row other than row 2, replace all the "2"s in the formula with the starting row number.

Let me know if it works.

Thanks Saagar,

But still it looks like there are problems. Is there any way I cam email you my Excel File.
my email address is dudhiam@gmail.com you can email me and I will send you my file


Problem is:

In column D, I want if there are 2, 1st_Signal in 15 days than at 2nd Signal in column C, I want to Notify Column D with lets say "1" for next 10 Days otherwise "0". (So I want "1" for next 10 days when there are 2 1st_signal in column C, Starting "1" from 2nd signal in Column D for 10Days.)

I know I sound confusing but it is hard to put it clear in words.


Thanks soo much Saagar.

If row 15 has "1st_signal" and row 20 has "1st_signal", you want row 20 and row 21-30 in column D to say "1". What if row 25 also has "1st-signal". Do you want the "1" to repeat until row 35 in column D?

I will send you a PM with my email address. Send me your file and provide an example in your file with your requirements.

Don't expect an immediate reply. I will try to reply when I can.

email me on dudhiam@gmail.com

Will be waiting for you email.