# Counting a certain consecutive pair of values

1. ## Counting a certain consecutive pair of values

1
1
2
1
1
2
2
3
2
1

Hi, so I would like to count how often pairs of (1,1) and (1,2), and (2,1) occur in this range. Results should be 2 for all three pairs. Please note that I would also like to skip the exclusion of a pair due to other numbers crossing in between such as 3 in this case of (1,3,1) which should still be counted as 1 pair of (1,1). Also I would also like to be able to count more than 2 consecutive pairs, for example (1,1,1) or (1,1,2) or (2,2,1) and so on: the more the better! Please let me know how I can achieve this using just formulas and no VBA.

2. ## Re: Counting a certain consecutive pair of values

Hi.

Your decscription began to confuse me aftet eh section beginning "Please note". This supplies the answers required in the first sentence.

=SUMPRODUCT((\$A\$2:\$A\$10=\$C\$2)*(\$A\$3:\$A\$11=\$C\$3))

3. ## Re: Counting a certain consecutive pair of values

Apologize for the miscommunication, I have attached a book that serves as a better source range for my needs. Please take a look if you dont mind!

P.S. The consecutive pairs should only be cut by 1 and 2, and nothing else. for example. 1,1,1,2,1,3,1 should return as 2 for pair (1,2), and not 1 because 3 is between the second (1,1) pair.

4. ## Re: Counting a certain consecutive pair of values

I think it's more confusing now!!

For 2,2... Rows 3&5 and counts rows 5&7 does not??
For 2,1 3&4 and 7&8 both count??

So each digit can only be counted once... is that the logic??

5. ## Re: Counting a certain consecutive pair of values

No... that can't be right, because 1,1 would then give 4. I'm lost...

6. ## Re: Counting a certain consecutive pair of values

yea so lets look at the first pair (1,1). so theres not a pair until row8 and row 10, (any other value does not cut pairs), and then row 12, row 13, and then row14,row15. so 3 pairs for (1,1).

7. ## Re: Counting a certain consecutive pair of values

I'm not clear, either, as based on the description, I don't understand some of your suggested answers.

Does this work for the first four rows?

=SUMPRODUCT((\$A\$2:\$A\$15=--LEFT(C2,1))*(\$A\$3:\$A\$16=--RIGHT(C2,1)))

I don't believe there is a 2,2 pair there.

8. ## Re: Counting a certain consecutive pair of values

1,1 2&4, 8&10, 12&13, 14&15 makes four, not 3???

9. ## Re: Counting a certain consecutive pair of values

Ugh! Don't get it ... Over to Glenn!!!

10. ## Re: Counting a certain consecutive pair of values

BtW, what part of Myanmar are you in? I spent a week working in Yangon in 2010 and 3 weeks travelling round the country in 2019. Lovely country and delicious food!!

11. ## Re: Counting a certain consecutive pair of values

2&4 is a no go, since row 3 contains 2, which cuts pairs of 1,1. Oh did you!? Glad that you enjoyed it whilst here! Food is amazing(especially street food)!

Edit: Forgot to mention I'm from Yangon too lol! But I think you still would have caught that haha. Still don't recommend visiting us anytime soon, it's chaotic right now with the military seizing power

12. ## Re: Counting a certain consecutive pair of values

I think rephrasing 1 as Wins, and 2 as Losses, and 3 and any other numerical value as Tie would clear things up more? So I want to count how many (Win,Win) happen in a row without a Loss in between, and count (Lose, Lose) without a Win in between. Tie should not cut the streaks (or pairs).

Likewise for (Win,Lose), (Lose,Win) and (Win,Win,Win) and (Lose,Lose,Lose) etc!

13. ## Re: Counting a certain consecutive pair of values

OK. I understand now... but I think this one is way above my "pay grade". I probably won't be able to contribute any more... unless inspiration hits me later.

14. ## Re: Counting a certain consecutive pair of values

=LET(z,A2:A16,p,C2:C7,y,TEXTJOIN(",",,FILTER(z,z<3)),LEN(SUBSTITUTE(y,p,p&1))-LEN(y))

15. ## Re: Counting a certain consecutive pair of values

I hoped you'd pick up on this Bo_Ry!! However, I **THINK** your answer for 2,2,1 is incorrect as there's a 3 in the sequence: 2,3,2,1 which breaks the sequence (if I understand correctly!!).

16. ## Re: Counting a certain consecutive pair of values

Glenn, I think 3 is for skip not for break.

Originally Posted by Hein Htut Oo
Please note that I would also like to skip the exclusion of a pair due to other numbers crossing in between such as 3 in this case of (1,3,1) which should still be counted as 1 pair of (1,1)

17. ## Re: Counting a certain consecutive pair of values

Not sure... see first sentence of Post 11. Anyhow, it's late evening where Hein is.. and it's saturday night, so we might not hear anything more today!

18. ## Re: Counting a certain consecutive pair of values

I like the use of helper columns, so when i look back after 1 year I still understand the method of solving the question.
I am sure it can be done with less helper steps which can be important when your actual data is numerous.

19. ## Re: Counting a certain consecutive pair of values

woah, this is exactly what I need! and to think you even picked up on my mistake for 2,2,1! Nice one, though I have to ask: is there a difference between =LET(z,A2:A16,p,C2:C7,y,TEXTJOIN(",",,FILTER(z,z<3)),LEN(SUBSTITUTE(y,p,p&1))-LEN(y)) and =LET(z,A2:A16,p,C2:C7,y,CONCAT(","&FILTER(z,z<3)),LEN(SUBSTITUTE(y,p,p&1))-LEN(y))? From what I understand they are the same right? Thank you for making such quick work of this! Cheers sir!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1