Consecutive Days

  • Thread starter Thread starter Gary Thomson
  • Start date Start date
G

Gary Thomson

I have the following strings in the following cells:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 a b bc bd be bcd bd d


For each day, I want to know how many consecutive days one
of the letters has appeared in that cell (where two
letters appear, we take the maximum duration of the two).

The answers would be displayed in the range J2:Q2 as
follows:

A B C D E F G H
1 1-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 8-Feb
2 1 1 2 3 4 5 6 3

Since:

On the 1st feb, "a" has appeared in 1 consecutive cell

On the 2nd Feb, "b" has appeared in one consecutive cell

On the 3rd feb, "b" has appeared in 2 consecutive
cells, "c" has appeared in 1 consecutive cell, so we take
the maximum

and so on.....

..

How can I do this?
 
Hi Gary
is there a maximum of characters for each cell (lets say at the max 3
chars)?

Frank
P.S.: please don't multipost this question to multiple groups
 
Yes, max characters is 12, although I could reduce this if
the formula become too massive.
 
Gary,

If you don't mind using a User-Defined-Function, then copy the code below
and paste it into a regular code module in the workbook, then enter

=maxApp($A$2:A2)

in cell A3, and copy as far right as you have data.

HTH,
Bernie
MS Excel MVP

Function MaxApp(inRange As Range) As Integer
Dim myVal As String
Dim i As Integer
Dim j As Integer
Dim TempMax As Integer
Dim TempVal As Integer

MaxApp = 1

myVal = inRange(inRange.Cells.Count).Value
For i = 1 To Len(myVal)
TempVal = 1
For j = inRange.Cells.Count - 1 To 1 Step -1
If InStr(1, inRange(j).Value, Mid(myVal, i, 1)) Then
TempVal = TempVal + 1
Else
Exit For
End If
MaxApp = Application.Max(MaxApp, TempVal)
Next j
Next i

End Function
 
Back
Top