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

Posted on 2012-09-20
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

    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

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    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.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now