Need help counting consecutive duplicate values

[winds]

Hello. I have a column which will have a bunch of 1's, 2's and 3's in random order and I want to know the max number of times the 1's, 2's and 3's occur consecutively. Can anyone help me devise such a formula?

 

[JamesCanale]

MrExcelPlayground7.xlsx
	A	B	C	D
1	3		1	2
2	3		2	1
3	1		3	4
4	2
5	1
6	1
7	2
8	3
9	1
10	1
11	3
12	2
13	1
14	2
15	1
16	2
17	3
18	3
19	3
20	2
21	3
22	3
23	3
24	3
25	1
26	3
27	1
28	2
29	1
30	2
Sheet20

Cell Formulas
Range	Formula
A1:A30	A1	=RANDARRAY(30,1,1,3,TRUE)
D1:D3	D1	=LEN(MAX(FILTERXML("<x><y>"&SUBSTITUTE(TRIM(CONCAT(IF(A$1#=C1,1," ")))," ","</y><y>")&"</y></x>","//y")))
Dynamic array formulas.

 

[winds]

Hmm this looks exactly what I want, but when I copy paste the formula my excel can't seem to recognise it.. any ideas why that seems to be the case?

I get this error as displayed in the image.

I also can't seem to copy paste the sheet above into my excel. The numbers in Column A doesn't seem to come out.

 

[Fluff]

any ideas why that seems to be the case?

That's because the filterxml function doesn't exist on a Mac.
How about

+Fluff 1.xlsm
	A	B	C	D
1	2		1	2
2	3		2	3
3	3		3	6
4	3
5	3
6	3
7	3
8	2
9	2
10	3
11	3
12	1
13	1
14	2
15	3
16	1
17	2
18	3
19	2
20	1
21	2
22	2
23	2
24	1
25	2
26	2
27	1
28	2
29	3
30	2
Master

Cell Formulas
Range	Formula
D1:D3	D1	=MAX((FREQUENCY(IF($A$1:$A$30=C1,ROW($A$1:$A$30)),IF($A$1:$A$30<>C1,ROW($A$1:$A$30)))))

 

[winds]

That's because the filterxml function doesn't exist on a Mac.
How about

+Fluff 1.xlsm
	A	B	C	D
1	2		1	2
2	3		2	3
3	3		3	6
4	3
5	3
6	3
7	3
8	2
9	2
10	3
11	3
12	1
13	1
14	2
15	3
16	1
17	2
18	3
19	2
20	1
21	2
22	2
23	2
24	1
25	2
26	2
27	1
28	2
29	3
30	2
Master

Cell Formulas
Range	Formula
D1:D3	D1	=MAX((FREQUENCY(IF($A$1:$A$30=C1,ROW($A$1:$A$30)),IF($A$1:$A$30<>C1,ROW($A$1:$A$30)))))

Hmm now I'm getting the "#VALUE!" error in my cell where I have inputted the formula. I tried copy pasting the sheet above and it also gives me the "#VALUE!" error as well. Any ideas why this is happening?

 

[Fluff]

Forgot to mention that you will need to confirm the formula with Ctrl Shift Enter, rather than just Enter.

 

[winds]

Forgot to mention that you will need to confirm the formula with Ctrl Shift Enter, rather than just Enter.

Wow it works! Now one more question, how do I adjust that formula so that if it is 1, 2, or 3, it will calculate that trend as well. The different number would be 0. For example if the order in the column is 1,1,2,2,3,3,0,0,1,1,2,0,2,3,1,0 in this case the highest 'trend' is 6

 

[Peter_SSs]

Try this, still confirmed with C+S+E

=MAX((FREQUENCY(IF($A$1:$A$16<>0,ROW($A$1:$A$16)),IF($A$1:$A$16=0,ROW($A$1:$A$16)))))

 

[winds]

Try this, still confirmed with C+S+E

=MAX((FREQUENCY(IF($A$1:$A$16<>0,ROW($A$1:$A$16)),IF($A$1:$A$16=0,ROW($A$1:$A$16)))))

It works, thank you so much!

 

[Peter_SSs]

Your welcome. Glad to assist.