Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Counting number of times "x" number apears consecutively

0

I was wondering if it's possible to count how many times does "x" number apear consecutively untill first "y" consecutive apear. Example:

  • 1                   1=2   
  • 2                   2=1
  • 1                   3=0
  • 1                   4=1
  • 1                   5=1
  • 1
  • 2     "x" would be number 1 in colum
  • 2     "y" would be 5 times consecutive
  • 1
  • 1
  • 2
  • 1
  • 2
  • 1
  • 1
  • 1
  • 1
  • 1

Another example: let's say you have RANDBETWEEN(1,2) set in a colum from A1 to A1001. Now I'm wondering if exel could count how many times does number 1 apear standing alone between two 2's, how many times does number 1 apear two times consecutively, three times consecutively, four times consecutively and so on untill lets say it counts the first seven time consecutive, then it stops counting. If you check the uper example, number 1 apeared two times standing alone, two times it apeared two times consecutively, zero times it apeared three times consecutively, one time it apeared four times consecutively and one time it apeared five times consecutively

excel
MS Office Version: Exel 2007
posted by: Dserec on Sun Sep 24, 2017 at 3:25 pm
Answer
Discuss

Discussion

Please rephrase your question to use the word "row" to strictly mean a horizontal sequence of cells in a spreadsheet and a "column" to be a vertical one. My preliminary answer to your question - still not fully understood - is that it is possible only with the use of VBA. I know of no simple Excel function that would extract the largest number of conseceutive occurrences of a value.
Variatus (rep: 4889) Sep 24, '17 at 9:38 pm
Well I'm just totally lost here after seeing the example lol.
don (rep: 1989) Sep 25, '17 at 3:24 am
Add to Discussion

Answers

0

Create a string of your numbers, like "1212221121211112221". (If you don't know how to create this string from A1:A1000, ask another question.) For the purpose of this exercise, place this string in cell A7 of a blank worksheet. Be sure to format that cell as Text. Fill B7:H7 with the numbers from 1 to 7, representing the consecutive repetitions. Write 1 into A8 and 2 into A9, representing your numbers 1 and 2 (I would have preferred 0 and 1 since we are digital :-). The formula permits this change). Now write this formula into B8.

=LEN(SUBSTITUTE($A$7,REPT($A8,B$7)&(SUM($A$8:$A$9)-$A8),REPT($A8,B$7)&(SUM($A$8:$A$9)-$A8)&"*"))-LEN($A$7)

Copy from there to B8:H9. The formula should calculate the recurrences you are interested in.

The formula uses the REPT() function to create strings like "1112" or "2221" with reference to the numbers in B7:H7 and A8:A9. It then converts all occurrences of these strings in the search string into "1112*" or "2221*", adding one character to each. The number of occurrences is equal to the number of added characters.

posted by: Variatus (rep: 4889) on Mon Sep 25, 2017 at 10:49 pm
Discuss

Discussion

I regret to note that you didn't come back for this reply. So I guess you found a good solution elsewhere. Would you mind sharing it here, as a matter of interest?
Variatus (rep: 4889) Sep 27, '17 at 11:22 pm
Actualy I didn't find a solution nowhere. I need the numbers to be in a column from A1:A1001 using RANDBETWEEN(1,2) to set numbers. If you can't make a functions that would count from that range then I cant do this
Dserec Oct 1, '17 at 4:07 am
Have you tried my solution? It starts with all numbers in A7 (could be anywhere, with minor adjustments to the formula). If that formula works for you the problem shifts from how to evaluate the string to how to create it. As I said, that is another question because this is a question & answer forum. But it's a much simpler one. Consider it solvable.
The steps to take are (a) Find out if the formula does what you want. If yes, proceed to (b) Ask a question how to convert an Excel range of 1000 cells containing single digit numbers to a string of those numbers without a separator between them. For that question you would need to specify whether the 1000 cells hold numbers or formulas evaluating to numbers.
Variatus (rep: 4889) Oct 1, '17 at 5:46 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login