Count how many sequence numbers

[chriscorpion786]

Hi All,

I need a formula to count the sequence of numbers from Product A to Product G, I have done it manually, but if I have thousands of rows, i need a formula to do this.

Product A	Product B	Product C	Product D	Product E	Product F	product G	Serial number sequence (Counted Max one)
125,666	125,667	15	556	557	558	559	4
450	666	667	668	669	670	888	5
333	566	475	123	987	100	9	0
100	101	102	103	104	105	106	7
98	99	100	99	98	31	32	3
56	57	88	87	10	11	12	3

 

[jasonb75]

This should do it

Book2
	A	B	C	D	E	F	G	H
1	Product A	Product B	Product C	Product D	Product E	Product F	product G	Max Sequence
2	125666	125667	15	556	557	558	559	4
3	450	666	667	668	669	670	888	5
4	333	566	475	123	987	100	9	0
5	100	101	102	103	104	105	106	7
6	98	99	100	99	98	31	32	3
7	56	57	88	87	10	11	12	3
Sheet4

Cell Formulas
Range	Formula
H2:H7	H2	=IFERROR(1/(1/MAX(FREQUENCY(IF((A2:F2+1)=B2:G2,COLUMN(A2:F2)),IF((A2:F2+1)<>B2:G2,COLUMN(A2:F2)))))+1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chriscorpion786]

Thanks, but could you be kind enough to explain a bit...how the formula works..

 

[jasonb75]

The first 'IF' creates an array of column numbers (column A =1, column B =2, etc) where the number in any given column is 1 greater than the number in the previous column. The second 'IF' returns the column numbers that are not in the first array by using a not equal logical test instead of an equal test. These arrays are used to create the data array and bins array for the frequency function (see excel help for more on this function).

Max returns the largest bin from the array, which is the count of the longest sequence of consecutive numbers. Due to the way that the formula works, the result will be 1 less than the actual count, so 1 is added at the end, but because a result of 1 would be incorrect when there are no consecutive sequence numbers, division by 0 is used to create an error and eliminate these rows before the 1 is added and the final result is shown in the cell.

 

[chriscorpion786]

Thank you Jason...very clear and have learnt a few ideas now...