Finding longest sequence of the same value in a row

[mrshift]

Hi all,

Hope someone can please help with this as I've spent hours scratching my head.

I'm looking for a formula that calculates the green values in the J column (which I've added in manually). I need the formula to please find the longest streak in the row of consecutive instances of the same positive integer value, i.e. consecutive blank cells are not counted.

	A	B	C	D	E	F	G	H	I	J
1	2						4	4	4	3
2	1	2	5	5	5	5		5	5	4
3	2	3	2	6	6	6	2	3	2	3

The integer itself with the longest streak in each column isn't known, nor am I interested in knowing this - just the length of the longest unbroken sequence of that integer, please.

Hope that makes sense, and thanks in advance!

 

[JoeMo]

Lightly tested UDF you can try. Use the UDF like you would any native worksheet function as in the example below.

Book2
	A	B	C	D	E	F	G	H	I	J
1	2						4	4	4	3
2	1	2	5	5	5	5		5	5	4
3	2	3	2	6	6	6	2	3	2	3
Sheet3

Cell Formulas
Range	Formula
J1:J3	J1	= MaxLength(A1:I1)

Function MaxLength(R As Range) As Variant
Dim V As Variant, i As Long, ct As Long
V = R.Value
For i = 1 To UBound(V, 2) - 1
    If V(1, i) > 0 And V(1, i) = V(1, i + 1) Then
        If ct = 0 Then ct = 1
        ct = ct + 1
        If ct > MaxLength Then MaxLength = ct
    Else
        ct = 0
    End If
Next i
End Function

 

[mrshift]

Many thanks JoeMo

Unfortunately, to me it looks like it's still counting blank cells as cells that I want counted if there's a row of them. I'd gladly share my Excel file or upload a mini sheet, but it's 1,000 columns wide - any suggestions welcome

To explain, there are a couple of unexpected results generated from this code that currently look like this:

	A	B	C	D	E	F	G	H	I	J
1	1	2	3	4	5	6	7	8	9	0
2										9

In other words, I've have expected the result in

1. J1 to be 1 (as there are integers in there, but all unique, so no sequences longer than 1), and
2. J2 to be 0 (as there are no integers in there at all)

Sorry this is fiddly!

 

[JoeMo]

If your row 2 is all blank cells, the function I posted returns 0 (in your J2) for me. But no matter, here's a modified function that needs more test time than I can devote to it. It would be useful if you could post some sample data, using XL2BB, that comprehends the spectrum of data contained in your excel file.

Book2
	A	B	C	D	E	F	G	H	I	J
1	2						4	4	4	3
2	1	2	5	5	5	5		5	5	4
3		5				7			9	1
4	1	2	3	4	5	6	7	8	9	1
5	1	2	9				5	4	1	1
6										0
Sheet3

Cell Formulas
Range	Formula
J1:J6	J1	= MaxLength(A1:I1)

Function MaxLength(R As Range) As Variant
Dim V As Variant, i As Long, ct As Long
V = R.Value
For i = 1 To UBound(V, 2) - 1
    If V(1, i) > 0 And V(1, i) = V(1, i + 1) Then
        If ct = 0 Then ct = 1
        ct = ct + 1
        If ct > MaxLength Then MaxLength = ct
    Else
        ct = 0
    End If
Next i
If MaxLength = 0 And Application.Sum(R) > 0 Then MaxLength = 1
End Function

 

[mrshift]

Many thanks again

Here's a small sample of the data:

Collatz conjecture.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	1
2		1	10	2	16	3	22	4	28	5	34	6	40	7	46	8	52	9	58	10	64	11	70	12	76	1
3	3		5	1	8	10	11	2	14	16	17	3	20	22	23	4	26	28	29	5	32	34	35	6	38	1
4	1		16		4	5	34	1	7	8	52	10	10	11	70	2	13	14	88	16	16	17	106	3	19	2
5			8		2	16	17		22	4	26	5	5	34	35	1	40	7	44	8	8	52	53	10	58	2
6			4		1	8	52		11	2	13	16	16	17	106		20	22	22	4	4	26	160	5	29	2
7			2			4	26		34	1	40	8	8	52	53		10	11	11	2	2	13	80	16	88	2
8			1			2	13		17		20	4	4	26	160		5	34	34	1	1	40	40	8	44	2
9						1	40		52		10	2	2	13	80		16	17	17			20	20	4	22	4
10							20		26		5	1	1	40	40		8	52	52			10	10	2	11	5
11							10		13		16			20	20		4	26	26			5	5	1	34	5
12							5		40		8			10	10		2	13	13			16	16		17	5
13							16		20		4			5	5		1	40	40			8	8		52	5
14							8		10		2			16	16			20	20			4	4		26	5
15							4		5		1			8	8			10	10			2	2		13	5
16							2		16					4	4			5	5			1	1		40	5
17							1		8					2	2			16	16						20	5
18									4					1	1			8	8						10	7
19									2									4	4						5	8
20									1									2	2						16	8
21																		1	1						8	16
22																									4	23
23																									2	23
24																									1	23
25																										24
Adjacent cells (2)

Cell Formulas
Range	Formula
C1:Y1	C1	=B1+1
Z1:Z25	Z1	= MaxLength(A1:Y1)
B2:Y25	B2	=IFERROR(IF(B1=1,"",IF(MOD(B1,2)=0,B1/2,$A$3*B1+$A$4)),"")

It looks like the function works until it begins displaying a result of 4 or above, where it's including blank cells as cells to be counted? Using this set of data, the function shouldn't be showing any results above 2.

Thanks for your time on this!

 

[Peter_SSs]

Using standard worksheet functions, you could try this

22 05 08.xlsm
	A	B	C	D	E	F	G	H	I	J
1	2						4	4	4	3
2	1	2	5	5	5	5		5	5	4
3	2	3	2	6	6	6	2	3	2	3
4										0
5	3									1
6									3	1
Streak

Cell Formulas
Range	Formula
J1:J6	J1	=IF(COUNT(A1:I1),MAX(FREQUENCY(IF(B1:I1<>"",IF(B1:I1=A1:H1,COLUMN(A1:H1))),IF(B1:I1<>"",IF(B1:I1<>A1:H1,COLUMN(A1:H1)))))+1,0)

 

[Peter_SSs]

Sorry, I had not seen your post #5 sample data. Formula adapted to that set of columns.

22 05 08.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	1
2		1	10	2	16	3	22	4	28	5	34	6	40	7	46	8	52	9	58	10	64	11	70	12	76	1
3	3		5	1	8	10	11	2	14	16	17	3	20	22	23	4	26	28	29	5	32	34	35	6	38	1
4	1		16		4	5	34	1	7	8	52	10	10	11	70	2	13	14	88	16	16	17	106	3	19	2
5			8		2	16	17		22	4	26	5	5	34	35	1	40	7	44	8	8	52	53	10	58	2
6			4		1	8	52		11	2	13	16	16	17	106		20	22	22	4	4	26	160	5	29	2
7			2			4	26		34	1	40	8	8	52	53		10	11	11	2	2	13	80	16	88	2
8			1			2	13		17		20	4	4	26	160		5	34	34	1	1	40	40	8	44	2
9						1	40		52		10	2	2	13	80		16	17	17			20	20	4	22	2
10							20		26		5	1	1	40	40		8	52	52			10	10	2	11	2
11							10		13		16			20	20		4	26	26			5	5	1	34	2
12							5		40		8			10	10		2	13	13			16	16		17	2
13							16		20		4			5	5		1	40	40			8	8		52	2
14							8		10		2			16	16			20	20			4	4		26	2
15							4		5		1			8	8			10	10			2	2		13	2
16							2		16					4	4			5	5			1	1		40	2
17							1		8					2	2			16	16						20	2
18									4					1	1			8	8						10	2
19									2									4	4						5	2
20									1									2	2						16	2
21																		1	1						8	2
22																									4	1
23																									2	1
24																									1	1
25																										0
Streak (2)

Cell Formulas
Range	Formula
C1:Y1	C1	=B1+1
Z1:Z25	Z1	=IF(COUNT(A1:Y1),MAX(FREQUENCY(IF(B1:Y1<>"",IF(B1:Y1=A1:X1,COLUMN(A1:X1))),IF(B1:Y1<>"",IF(B1:Y1<>A1:A1,COLUMN(A1:X1)))))+1,0)
B2:Y25	B2	=IFERROR(IF(B1=1,"",IF(MOD(B1,2)=0,B1/2,$A$3*B1+$A$4)),"")

 

[mrshift]

Many thanks indeed Peter, really appreciate it

Though it looks like there's a snag with it - from a small excerpt of the data:

Collatz conjecture.xlsx
	ALE	ALF	ALG	ALH	ALI	ALJ	ALK	ALL	ALM
1	993	994	995	996	997	998	999	1000	1
2	2980	497	2986	498	2992	499	2998	500	1
3	1490	1492	1493	249	1496	1498	1499	250	1
4	745	746	4480	748	748	749	4498	125	2
5	2236	373	2240	374	374	2248	2249	376	2
6	1118	1120	1120	187	187	1124	6748	188	2
7	559	560	560	562	562	562	3374	94	2
8	1678	280	280	281	281	281	1687	47	2
Adjacent cells

Cell Formulas
Range	Formula
ALE1:ALL1	ALE1	=ALD1+1
ALM1:ALM8	ALM1	=IF(COUNT(A1:ALL1),MAX(FREQUENCY(IF(B1:ALL1<>"",IF(B1:ALL1=A1:ALK1,COLUMN(A1:ALK1))),IF(B1:ALL1<>"",IF(B1:ALL1<>A1:A1,COLUMN(A1:ALK1)))))+1,0)
ALE2:ALL8	ALE2	=IFERROR(IF(ALE1=1,"",IF(MOD(ALE1,2)=0,ALE1/2,$A$3*ALE1+$A$4)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:ALJ,ALM:XEZ	Cell	contains a blank value	text	NO
A:ALJ,ALM:XEZ	Expression	=OR(OFFSET(B1,0,-1)=OFFSET(A1,0,-1),OFFSET(B1,0,-1)=OFFSET(C1,0,-1))	text	NO
ALL250:ALL1048576,ALL1:ALL19	Cell	contains a blank value	text	NO
ALL250:ALL1048576,ALL1:ALL19	Expression	=OR(OFFSET(#REF!,0,-1)=OFFSET(ALL1,0,-1),OFFSET(#REF!,0,-1)=OFFSET(#REF!,0,-1))	text	NO
ALK248:ALK1048576,ALK1:ALK19	Cell	contains a blank value	text	NO
ALK248:ALK1048576,ALK1:ALK19	Expression	=OR(OFFSET(ALL1,0,-1)=OFFSET(ALK1,0,-1),OFFSET(ALL1,0,-1)=OFFSET(#REF!,0,-1))	text	NO

The bottom two rows should be reporting a 3, not a 2?

 

[mrshift]

PS sorry for the crazy conditional formatting - accidentally included it! ?‍

 

[Peter_SSs]

Though it looks like there's a snag with it

Yes, in post #7 I did not correctly adjust the post #6 formula.

The formula in ALM1 of post #8 is
=IF(COUNT(A1:ALL1),MAX(FREQUENCY(IF(B1:ALL1<>"",IF(B1:ALL1=A1:ALK1,COLUMN(A1:ALK1))),IF(B1:ALL1<>"",IF(B1:ALL1<>A1:A1,COLUMN(A1:ALK1)))))+1,0)

It should be
=IF(COUNT(A1:ALL1),MAX(FREQUENCY(IF(B1:ALL1<>"",IF(B1:ALL1=A1:ALK1,COLUMN(A1:ALK1))),IF(B1:ALL1<>"",IF(B1:ALL1<>A1:ALK1,COLUMN(A1:ALK1)))))+1,0)