I think to make this more efficient, you will need to create a helper column that concatenates all the event results for each person to create a pattern of 0's and 1's that you can reference.

In order to concatenate 100 columns, however, you will need a User Defined Function.

I have used the following function:

Code:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

To add the function to the work book, hit ALT+F11, then go to Insert|Module and paste the code into the editor.

Now go back to the worksheet and enter formula in next empty column (in your sample, column L).

=aconcat(B2:K2)

copied down.

Now you can use a simple COUNTIF() formula to look up and count the pattern.

An example in Z2 of pattern: 1000000001

Then the formula in AA2 would be:

=COUNTIF($L$2:$L$30;Z2) which can be copied down to count other patterns.

Hope this helps.

## Bookmarks