+ Reply to Thread
Results 1 to 19 of 19

Counting a certain consecutive pair of values

  1. #1
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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))
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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.
    Attached Files Attached Files
    Last edited by Hein Htut Oo; 01-15-2022 at 04:55 AM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #6
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,542

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Counting a certain consecutive pair of values

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

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,542

    Re: Counting a certain consecutive pair of values

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

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #11
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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
    Last edited by Hein Htut Oo; 01-15-2022 at 06:06 AM.

  12. #12
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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!
    Last edited by Hein Htut Oo; 01-15-2022 at 06:08 AM.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,895

    Re: Counting a certain consecutive pair of values

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

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #16
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,895

    Re: Counting a certain consecutive pair of values

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

    Quote Originally Posted by Hein Htut Oo View Post
    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. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #18
    Valued Forum Contributor
    Join Date
    10-14-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2019
    Posts
    332

    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.
    Attached Files Attached Files
    Kind regards,
    Piet Bom

  19. #19
    Registered User
    Join Date
    10-29-2021
    Location
    Myanmar
    MS-Off Ver
    Office 365
    Posts
    33

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Counting consecutive values?
    By Elijah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-29-2020, 12:23 PM
  2. [SOLVED] Counting a pair of values
    By audax48 in forum Excel General
    Replies: 4
    Last Post: 11-19-2019, 05:23 PM
  3. Counting consecutive values less than a .9
    By mckenziegolf in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-16-2017, 12:05 PM
  4. [SOLVED] (HELP) Counting Consecutive Values < 1 From Right
    By mus1ca in forum Excel General
    Replies: 6
    Last Post: 07-22-2014, 08:51 AM
  5. [SOLVED] Counting Consecutive Values
    By khauskins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2012, 11:37 AM
  6. Counting consecutive values
    By Elijah in forum Excel General
    Replies: 7
    Last Post: 04-23-2010, 05:13 PM
  7. pair up data from consecutive cells in a column
    By seafarer in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-22-2008, 02:55 AM

Bookmarks

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