Count consecutive numbers in Array

3

6

7

1

4

1

0

2

5

1

2

3

6

0

1

2

4

7

3

0

6

5

8

6

9

7

10

8

11

9

audax48

Cell Formulas
Range	Formula
L2:L11	L2	=SUM(N(( INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("2:"&COUNT($H$2:$H$7) )) ) - INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("1:"&COUNT($H$2:$H$7)-1)) ) ) = 1))

audax48 · May 16, 2022

KRice said:
Try this:

MrExcel_20220515.xlsx
H I J K L
1 3-column Inputs Match Value Number of Consecutive Matches
2 3 4 5 0 [FORMULA='=SUM(N((
audax48

MrExcel_20220515.xlsx

audax48

audax48 · May 16, 2022

audax48 said:
Good day Experts..please help me with this
I have 3 Columns with single digit numbers, I need to know how many times each digit repit consecutively.ex
345
367
410
123
012
306
here we can see 0 repit 1 times (rows 5, 6), 1 = 2(rows 3, 4 and 4, 5), 3 1 times (rows 1 and 2)
please help with the formula, thanks

Hi

audax48 said:
Good day Experts..please help me with this
I have 3 Columns with single digit numbers, I need to know how many times each digit repit consecutively.ex
345
367
410
123
012
306
here we can see 0 repit 1 times (rows 5, 6), 1 = 2(rows 3, 4 and 4, 5), 3 1 times (rows 1 and 2)
please help with the formula, thanks

Thank you for your answer, I can not get the numbers you got, I get 0003000000, in Column P, show mw that the numb#ers 3 repeat once and entered with CONTROL, SHIFT AND ENTER. WITHOUT THAT I GET #REF

KRice · May 16, 2022

Can you upload the mini-sheet with the XL2BB add-in showing the 3-column inputs, the results, and the formula?

audax48 · May 16, 2022

KRice said:
Try this:

MrExcel_20220515.xlsx
H I J K L
1 3-column Inputs Match Value Number of Consecutive Matches
2 3 4 5 0 1
3 3 6 7 1 2
4 4 1 0 2 1
5 1 2 3 3 1
6 0 1 2 4 0
7 3 0 6 5 0
8 6 0
9 7 0
10 8 0
11 9 0
audax48
Cell Formulas
Range Formula
L2:L11 L2 =SUM(N(( INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))*((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("2:"&COUNT($H$2:$H$7) )) ) - INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))*((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("1:"&COUNT($H$2:$H$7)-1)) ) ) = 1))

KRice said:
Can you upload the mini-sheet with the XL2BB add-in showing the 3-column inputs, the results, and the formula?

MultiplicaTusLucas3.xlsm

H

I

J

K

L

M

2

3

4

5

0

3

6

7

1

4

1

0

2

1

2

3

4

6

0

1

2

4

7

3

0

6

5

8

6

9

7

10

8

11

9

12

Sheet1

Cell Formulas
Range	Formula
L2:L11	L2	=SUM(LEN((INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("2:"&COUNT($H$2:$H$7) )) ) -INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$7=$K2))>0),0),ROW(INDIRECT("1:"&COUNT($H$2:$H$7)-1)) )) = 1))
Press CTRL+SHIFT+ENTER to enter array formulas.

KRice said:
Can you upload the mini-sheet with the XL2BB add-in showing the 3-column inputs, the results, and the formula?

MultiplicaTusLucas3.xlsm

H

I

J

K

L

M

5

1

2

6

0

6

8

2

1

7

5

7

9

2

8

0

6

0

3

9

4

2

4

10

0

7

2

5

11

9

7

2

6

12

3

6

7

13

7

4

7

8

14

6

7

6

9

15

2

4

3

16

5

2

4

17

2

9

4

18

3

8

1

19

2

1

5

20

6

3

2

21

5

8

22

8

9

8

Sheet1

Cell Formulas
Range	Formula
M5:M14	M5	=SUM(N(( INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$4=$K2))>0),0),ROW(INDIRECT("2:"&COUNT($H$2:$H$7) )) ) - INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$4=$K2))>0),0),ROW(INDIRECT("1:"&COUNT($H$2:$H$7)-1)) ) ) = 1))
Press CTRL+SHIFT+ENTER to enter array formulas.

audax48 · May 16, 2022

Let see the following example

MultiplicaTusLucas3.xlsm

H

I

J

K

L

5

1

2

6

0

6

8

2

1

7

5

7

9

2

8

0

6

0

3

9

4

2

4

10

0

7

2

5

11

9

7

2

6

12

3

6

7

13

7

4

7

8

14

6

7

6

9

15

2

4

3

16

5

2

4

17

2

9

4

18

3

8

1

19

2

1

5

20

6

3

2

21

5

8

22

8

9

8

Sheet1

Cell Formulas
Range	Formula
L5:L22	L5	=SUM(N(( INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$5:$H$22)))((($H$5:$H$22=$K5)+($I$5:$I$22=$K5)+($J$5:$J$22=$K5))>0),0),ROW(INDIRECT("2:"&COUNT($H$5:$H$22) )) ) - INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$5:$H$22)))((($H$5:$H$22=$K5)+($I$5:$I$22=$K5)+($J$5:$J$22=$K5))>0),0),ROW(INDIRECT("1:"&COUNT($H$5:$H$22)-1)) ) ) = 1))
Press CTRL+SHIFT+ENTER to enter array formulas.

KRice · May 16, 2022

The 1st version you posted with the LEN function is not correct....it should be the N function, as shown in post #2. The 2nd version you posted uses a different data set spread over a different range, but you didn't adjust the ranges in the formulas to match. These 4's are not correct when all of the other ranges end at row 7...but all of the ranges for the 3-column numbers need to be adjusted in this example because the data are found in rows 5:22. And all of the numbers to match from 0-9 are now in column L not K, so those need to be adjusted too.
=SUM(N((
INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))*((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$4=$K2))>0),0),ROW(INDIRECT("2:"&COUNT($H$2:$H$7) )) ) -
INDEX(IFERROR(ROW(INDIRECT("1:"&COUNT($H$2:$H$7)))*((($H$2:$H$7=$K2)+($I$2:$I$7=$K2)+($J$2:$J$4=$K2))>0),0),ROW(INDIRECT("1:"&COUNT($H$2:$H$7)-1)) )
) = 1))

KRice · May 16, 2022

You might want to edit your last post to delete the code.

KRice · May 16, 2022

The last example appears to be correct, except the formula is pulled down no further than 9, since you are making a list of the number of repeats for numerals 0..9.

MrExcel_20220515.xlsx

H

I

J

K

L

5

1

2

6

0

6

8

2

1

7

5

7

9

2

6

8

0

6

0

3

9

4

2

4

10

0

7

2

5

11

9

7

2

6

12

3

6

7

13

7

4

7

8

14

6

7

6

9