Create a macro: formula to show count how many consecutive alphabet in a spreadsheet (2)

Posted on 2012-09-20
Medium Priority
Last Modified: 2012-12-29
With reference to this question, further criteria are set in counting consecutive months:

1. as soon as consecutive 6 months is reached, the consecutive months count will restart again. For example, Jan-Jun2012= 6 consecutive months. July to Sept 2012 will be another 3 consecutive months. and so on. there is no running /overlapping calculation.
Thus max consecutive 3 months = 4 times and max consecutive 6 times = 2 times in a year.

2. Consecutive months to be counted within each year only (for example, if it is Oct-Dec 2011, and then Jan -Mar 2012, it can only be counted consecutive 3 times for 2011 and 2012 respectively and NOT 6 times.
Question by:ceneiqe
LVL 24

Accepted Solution

Steve earned 900 total points
ID: 38422203
The following formula change will allow for part 1 of the question:
Function ConsecutiveCount(Letter As String, Occurence As Integer, TheRange As Range) As Integer
Dim i As Long
For Each c In TheRange
    If c.Value Like "*" & Letter & "*" Then
        i = i + 1
        If i >= Occurence Then
            ConsecutiveCount = ConsecutiveCount + 1
            i = 0
        End If
        i = 0
    End If
End Function

Open in new window

as for the second part of the question:
the data is not in bands of more than one year, so it should not matter.
If your data is set out diferently to allow for more years in the columns, post an example and will see how to make the formula work.

Author Comment

ID: 38425710
Thus max consecutive 3 months = 4 times and max consecutive 6 times = 2 times in a year.

sorry, i think i typed wrongly  max consecutive 3 months = 3 times - it will be counted as 3 times when there is a break in between, ie.
Jan-Mar, May-July, Sept-Nov=3 times

For example, a row 52 there is a hit from Apr - Dec- this should be
Apr-Jun - consecutive 3
Jul- Sept - consecutive 3 but since from Apr-Sept, therefore upgrade to consecutive 6
Oct-Dec- since hit a max of consecutive 6, thus reset back to consecutive 3

Consecutive 3= 2 times
Consecutive 6= 1 time

2nd part of question:
yes i agree with you, since it is on a per year basis, so no overlapping.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question