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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,551
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,551
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,057
Messages
5,466,321
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top