Count how many sequence numbers

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
72
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 AProduct BProduct CProduct DProduct EProduct Fproduct GSerial number sequence (Counted Max one)
125,666125,667155565575585594
4506666676686696708885
33356647512398710090
1001011021031041051067
9899100999831323
565788871011123
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,538
Office Version
2019
Platform
Windows
This should do it
Book2
ABCDEFGH
1Product AProduct BProduct CProduct DProduct EProduct Fproduct GMax Sequence
212566612566715556557558559
4
3450666667668669670888
5
43335664751239871009
0
5100101102103104105106
7
6989910099983132
3
756578887101112
3
Sheet4
Cell Formulas
RangeFormula
H2:H7H2=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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,538
Office Version
2019
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,007
Messages
5,465,981
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top