Counting an locating consecutive scores.

[Marvo]

Hi. I have a workbook that contains football scores in Columns L & M. There are over 5,000 results. Would it be possible to have a formula to locate consecutive results? The reason I ask is I have been asked when was the last occurrence of 3 consecutive goalless games. I can't think of a way to do this except manually which is very time consuming. I did try one formula I saw elsewhere but I got the message "Excel ran out of resources".

Many thanks.

Cobblers.xlsm
	L	M
1	F	A
2	0	2
3	1	4
4	3	1
5	2	2
6	2	1
7	1	3
8	4	3
9	6	3
10	2	3
11	5	1
12	1	5
13	2	1
14	0	6
15	4	0
ALL

 

[Marvo]

Just as an add-on, I was thinking, maybe it might be easier to merge the cells so that a score would show in one cell as 0-0? That may be easier to find?

 

[Marvo]

Its okay, I've managed to do this myself. For anybody interested.

Step 1: Merged the cells
Step 2: Placed 0-0 in Cell CU2
Step 3: In Cell CR2 =IF(CQ2=$CU$2,1,0)
Step 4: In Cell CR3 =IF(CQ3=$CU$2,1+CR2,0) copied down.

Then used the filter. Turns out it had only happened once in 127 years.

I'm sure there was an easier way but have to say, quite proud of myself.