Count consecutive numbers in Array

A

audax48

Board Regular
Joined
May 19, 2012
Messages
54
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Try this:
MrExcel_20220515.xlsx
HIJKL
13-column InputsMatch ValueNumber of Consecutive Matches
234501
336712
441021
512331
601240
730650
860
970
1080
1190
audax48
Cell Formulas
RangeFormula
L2:L11L2=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))
 
Upvote 0
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
Click to expand...
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
Click to expand...
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
 
Upvote 0
Can you upload the mini-sheet with the XL2BB add-in showing the 3-column inputs, the results, and the formula?
 
Upvote 0
KRice said:
Try this:
MrExcel_20220515.xlsx
HIJKL
13-column InputsMatch ValueNumber of Consecutive Matches
234501
336712
441021
512331
601240
730650
860
970
1080
1190
audax48
Cell Formulas
RangeFormula
L2:L11L2=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))
Click to expand...

KRice said:
Can you upload the mini-sheet with the XL2BB add-in showing the 3-column inputs, the results, and the formula?
Click to expand...
MultiplicaTusLucas3.xlsm
HIJKLM
234505
336715
441025
512334
601245
730655
865
975
1085
1195
12
Sheet1
Cell Formulas
RangeFormula
L2:L11L2=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?
Click to expand...
MultiplicaTusLucas3.xlsm
HIJKLM
512602
682112
757922
806032
942242
1007252
1197262
1233672
1374782
1467692
15243
16524
17294
18381
19215
20632
21558
22898
Sheet1
Cell Formulas
RangeFormula
M5:M14M5=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.
 
Upvote 0
Let see the following example


MultiplicaTusLucas3.xlsm
HIJKL
512600
682111
757921
806030
942240
1007250
1197260
1233670
1374780
1467690
152430
165240
172940
183810
192150
206320
215580
228980
Sheet1
Cell Formulas
RangeFormula
L5:L22L5=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.
 
Last edited by a moderator:
Upvote 0
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))
 
Upvote 0
You might want to edit your last post to delete the code.
 
Upvote 0
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
HIJKL
512600
682112
757926
806030
942242
1007250
1197260
1233672
1374781
1467690
15243The formula stops above
16524
17294
18381
19215
20632
21558
22898
Sheet14
Cell Formulas
RangeFormula
L5:L14L5=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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Counting given amount of consecutives
2 3 4
Replies
37
Views
667
Marvo
M
I
  • Question
Formula to count duplicate Values every x # of rows
Replies
8
Views
266
Fluff
Fluff
A
  • Question
Macro to validate EAN code
Replies
5
Views
406
jimrward
jimrward
K
  • Question
Lottery Combinations Filter
Replies
2
Views
543
Fluff
Fluff
S
  • Solved
VBA Code Help
Replies
4
Views
277
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top