Matrix Help

[173]

I have a set of 4 columns with YES/NO statements in them.
I need to find any YES strings, of three or more, within one column, uninterupted by a YES in another colum.

EXAMPLE:
_____A ___B__C__D__E
1 1/1/2008 YES NO NO NO
2 1/2/2008 NO NO YES YES
3 1/3/2008 YES NO NO NO
4 1/4/2008 YES NO NO NO
5 1/5/2008 NO NO NO NO
6 1/6/2008 YES NO NO NO

B1:B4 would not meet this criteria because D2 and E2 fall in the middle of this yes string.

B3:B6 would meet this criteria. Any set of numbers that do meet this criteria need to be recorded with the last date that string falls on, the number of uninterupted YES's in that string, and the catagorie it falls under.

In this example, I need something like:
DATE_________No of Occurences/Catagorie
1/6/2008_______________3B


After multiple entries, it would look something like this:
1/ 6/2008______________3B
1/13/2008______________3A
1/19/2008______________3D
2/ 4/2008______________4C

Can something like this be done?

[mrice]

This macro might help. It seems to be easier to start at the bottom and work upwards.

Sub Test()
For N = Cells(65536, 1).End(xlUp).Row To 3 Step -1
    For M = 2 To 5
        If Cells(N, M) = "YES" Then
            YesCount = 1
            Counter = 1
            Do While (Cells(N - Counter, M) = "YES" Or (Cells(N - Counter, M) = "NO" And Application.CountIf(Range(Cells(N - Counter, 1), Cells(N - Counter, 4)), "YES") = 0))
                Counter = Counter + 1
                If Cells(N - Counter, M) = "YES" Then YesCount = YesCount + 1
                If N - Counter = 1 Then Exit Do
            Loop
            If YesCount >= 3 Then
                Cells(65536, 7).End(xlUp).Offset(1, 0) = Cells(N, 1)
                Cells(65536, 7).End(xlUp).Offset(0, 1) = YesCount & Chr$(M + 64)
            End If
        End If
    Next M
    N = N - Counter
Next N
End Sub

You can then sort your data by date.

[173]

Thank you!

[173]

Thanks
SOLVED

[DonkeyOte]

Purely as a heads up (given resolved) given changes made in 2007 it's now best to use Rows.Count as opposed to 65536:

eg this:

For N = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1

as opposed to this:

For N = Cells(65536, 1).End(xlUp).Row To 3 Step -1

This is purely down to the fact that 2007 has 1 million plus rows and thus you can not guarantee 100% of the time that the 65536 method will work pending version etc.... the Rows.Count method is essentially failsafe
(the same is true re: Columns.Count etc...)