Count consecutive match with previous row

[Kishan]

Using Excel 2000

Hi,

I need a formula to Count MAX Consecutive Match with previous row data
For example:
Check row8 with row7 find 2 consecutive matches
Check row12 with row11 find 4 consecutive matches
Check row16 with row15 find 3 consecutive matches
Check row20 with row19 find 8 consecutive matches

Example data...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5		Num	1	2	3	4	5	6	7	8	9	10	11	12	13	14		Count MAX Consecutive Match
6
7			X	1	1	1	2	1	2	1	2	X	1	1	X	1
8			X	X	2	1	X	X	1	1	2	1	X	2	1	1		2
9
10
11			X	1	X	2	1	2	X	X	X	1	1	1	X	X
12			1	1	X	1	2	1	X	X	X	1	2	1	X	1		4
13
14
15			2	1	X	1	1	X	1	1	1	2	1	1	1	2
16			1	1	1	1	1	2	1	1	2	1	X	X	X	1		3
17
18
19			1	1	1	1	1	2	1	1	1	2	1	1	1	2
20			1	1	1	1	1	2	1	1	2	1	X	X	X	1		8
21
22
Sheet1

Thank you in advance

Kishan

 

[Ells_]

No idea on a formula that could do this, but I could do it using VBA. Is VBA an option for you or does it have to be a formula?

A custom function doesn't seem too difficult for this. I'll edit this in a bit with a formula

 

[Ells_]

Function ConsecutiveMatches(arr1 As Range, arr2 As Range) As Long


If arr1.Count <> arr2.Count Then
    ConsecutiveMatches = -1
    Exit Function
End If


Dim maxMatches As Long: maxMatches = 0
Dim i As Long: i = arr1.Count


For n = 1 To i
    If arr1(n) = arr2(n) Then
        maxMatches = maxMatches + 1
    Else
        If maxMatches > ConsecutiveMatches Then
            ConsecutiveMatches = maxMatches
        End If
        maxMatches = 0
    End If
Next n


' for the case where max matches at end of array
If maxMatches > ConsecutiveMatches Then
    ConsecutiveMatches = maxMatches
End If


End Function

 

[Marcelo Branco]

Question
Why should the result for the set Row 15 - Row 16 be 3?
Neither M16 nor N16 nor O16 are the same as above.
Could you clarify?

M.

 

[Kishan]

Question
Why should the result for the set Row 15 - Row 16 be 3?
Neither M16 nor N16 nor O16 are the same as above.
Could you clarify?

M.

Hi Marcelo Branco, sorry my error here is the correct layout answer in the cell R16 should be 2.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4
5		Num	1	2	3	4	5	6	7	8	9	10	11	12	13	14		Count MAX Consecutive Match
6
7			X	1	1	1	2	1	2	1	2	X	1	1	X	1
8			X	X	2	1	X	X	1	1	2	1	X	2	1	1		2
9
10
11			X	1	X	2	1	2	X	X	X	1	1	1	X	X
12			1	1	X	1	2	1	X	X	X	1	2	1	X	1		4
13
14
15			2	1	X	1	1	X	1	1	1	2	1	1	1	2
16			1	1	1	1	1	2	1	1	2	1	X	X	X	1		2
17
18
19			1	1	1	1	1	2	1	1	1	2	1	1	1	2
20			1	1	1	1	1	2	1	1	2	1	X	X	X	1		8
21
22
Sheet1

Thank you

Regards,
Kishan

 

[Kishan]

No idea on a formula that could do this, but I could do it using VBA. Is VBA an option for you or does it have to be a formula?

A custom function doesn't seem too difficult for this. I'll edit this in a bit with a formula

Function ConsecutiveMatches(arr1 As Range, arr2 As Range) As Long


If arr1.Count <> arr2.Count Then
    ConsecutiveMatches = -1
    Exit Function
End If


Dim maxMatches As Long: maxMatches = 0
Dim i As Long: i = arr1.Count


For n = 1 To i
    If arr1(n) = arr2(n) Then
        maxMatches = maxMatches + 1
    Else
        If maxMatches > ConsecutiveMatches Then
            ConsecutiveMatches = maxMatches
        End If
        maxMatches = 0
    End If
Next n


' for the case where max matches at end of array
If maxMatches > ConsecutiveMatches Then
    ConsecutiveMatches = maxMatches
End If


End Function

Hi Ells_, I tried the "Function ConsecutiveMatches" it is prefect even for cell R16 it is giving the answer 2 which is correct "I did not noticed till Marcelo Branco mentioned that I had error in the row15 & 16"

It will be ok if you could make a VBA too.

Thank you

Regards,
Kishan

 

[Marcelo Branco]

A formula solution

Array formula in R8
=MAX(FREQUENCY(IF(C7:P7=C8:P8,COLUMN(C7:P7)),IF(C7:P7<>C8:P8,COLUMN(C7:P7))))
Ctrl+Shift+Enter

Then copy this formula (Ctrl+C)
and paste (Ctrl+V) in R12, R16, R20

Hope this helps

M.

 

[Kishan]

A formula solution

Array formula in R8
=MAX(FREQUENCY(IF(C7:P7=C8:P8,COLUMN(C7:P7)),IF(C7:P7<>C8:P8,COLUMN(C7:P7))))
Ctrl+Shift+Enter

Then copy this formula (Ctrl+C)
and paste (Ctrl+V) in R12, R16, R20

Hope this helps

M.

Hi Marcelo Branco, formula is resulting as request.

Thank you for your help

Regards,
Kishan