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
"Gary Thomson" <(E-Mail Removed)> wrote in message
news:d4d801c3eff7$4d4abc90$(E-Mail Removed)...
> 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?
|
Similar Threads
