Solved

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

Posted on 2012-09-20
302 Views
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.
0
Question by:ceneiqe

LVL 24

Accepted Solution

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
Else
i = 0
End If
Next
End Function
``````

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.
Consecutives-count.xls
0

Author Comment

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

Results
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.
0

## Join & Write a Comment Already a member? Login.

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!