Finding records that have 12 consecutive values.

  • Thread starter Captain Snuggles
  • Start date
C

Captain Snuggles

I'm trying to find all the people in my spreadsheet who have given
donations for 12 consecutive months in the last 60 months. I know how
to do a simple count of all donations in the last 60 months, but I'm
not sure how to write a formula that will identify those who gave
consecutively for any 12 month period in th last 60 months. Any
ideas?
This is how I have my spreadsheet formatted.

Name | Month1| Month2| Month3| Month4| | Month5| | Month6| |
Month7| | Month8| ...
Johan Doe| $10 | $5 | $0 | $100 | $100 |
$0 | $10 | $10 |
 
M

merjet

The following will make green any name who has contributed 12
consecutive months. Change rows and sheet name to suit.

Sub Macro1()
Dim iCol As Integer
Dim iCt As Integer
Dim iRow As Integer

For iRow = 2 To 6
iCt = 0
For iCol = 2 To 61
If Sheets("Sheet1").Cells(iRow, iCol) > 0 Then
iCt = iCt + 1
Else
iCt = 0
End If
If iCt = 12 Then
Sheets("Sheet1").Cells(iRow, 1).Interior.ColorIndex = 4
Exit For
End If
Next iCol
Next iRow
End Sub

Hth,
Merjet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top