+ Reply to Thread
Results 1 to 12 of 12

Check for 5 consecutive identical values

  1. 03-25-2016, 08:42 PM #1
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Question Check for 5 consecutive identical values

    Hi everyone... can I ask a question. How can I check for 5 consecutive identical values in may table. The formula should return true or false.
    Here's what I want to achieve in column J. I tried searching the internet and maybe it something to do with the function FREQUENCY but I'm not familiar with that function. Thank you.
    A.PNG
    Attachment 452831
    Attached Images Attached Images
    Last edited by mychael14; 03-25-2016 at 09:27 PM.
    Reply With QuoteRegister To Reply
  2. 03-25-2016, 09:06 PM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Check for 5 consecutive identical values

    Hi mychael,

    I can't see your picture, but find the attached workbook and see if it helps.
    Five consecutive all same.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 03-25-2016, 09:08 PM #3
    InvisibleMan
    InvisibleMan is offline
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Check for 5 consecutive identical values

    Something like:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-25-2016, 09:24 PM #4
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check for 5 consecutive identical values

    Thank you for the solution.... anyway can this be done with a single formula?
    (I upload the picture in web, you may see it now)
    Reply With QuoteRegister To Reply
  5. 03-25-2016, 09:29 PM #5
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check for 5 consecutive identical values

    Quote Originally Posted by InvisibleMan View Post
    Something like:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    I can't make it work.
    Reply With QuoteRegister To Reply
  6. 03-25-2016, 09:33 PM #6
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check for 5 consecutive identical values

    This what I have now.
    B.PNG

    The formula I used in second table
    Please Login or Register  to view this content.
    And at the end I check the occurrences of 5
    Reply With QuoteRegister To Reply
  7. 03-25-2016, 09:43 PM #7
    southward
    southward is offline
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Check for 5 consecutive identical values

    I borrowed this from Seiya @ http://www.mrexcel.com/forum/excel-q...tes-range.html

    Put this in a module.

    Please Login or Register  to view this content.
    and use this formula:

    Please Login or Register  to view this content.
    See attached spreadsheet.

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-25-2016, 10:01 PM #8
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check for 5 consecutive identical values

    Quote Originally Posted by southward View Post
    I borrowed this from Seiya @ http://www.mrexcel.com/forum/excel-q...tes-range.html

    Put this in a module.

    Please Login or Register  to view this content.
    and use this formula:

    Please Login or Register  to view this content.
    See attached spreadsheet.

    Cheers
    Thank you very much southward for the vbcodes.... I'll make sure that I reference this in future. But I can't use macro because my users don't know how to enable and deal with macro. I think they don't have any idea about it.
    Reply With QuoteRegister To Reply
  9. 03-25-2016, 10:34 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Check for 5 consecutive identical values

    Try array-entering this formula in cell I1 and filling down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    A
    A
    A
    A
    A
    A
    A
    FALSE
    		 =MAX(FREQUENCY(IF($A1:$H1="A",COLUMN(B:I)),IF($A1:$H1<>"A",COLUMN(A:H))))>=5
    2
    A
    A
    A
    A
    A
    TRUE
    3
    A
    A
    A
    A
    A
    A
    A
    TRUE
    4
    A
    A
    A
    A
    FALSE
    5
    A
    A
    A
    A
    A
    A
    A
    A
    TRUE
    Dave
    Reply With QuoteRegister To Reply
  10. 03-25-2016, 11:03 PM #10
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9
    Wow. That is great! Thank you very much Dave. I'll use your code in my excel. I'll mark this as solve.
    Reply With QuoteRegister To Reply
  11. 03-25-2016, 11:05 PM #11
    mychael14
    mychael14 is offline
    Registered User
    Join Date
    08-21-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check for 5 consecutive identical values

    Sorry I'm currently using my phone to comment. I'm unable to mark this as solve.
    Reply With QuoteRegister To Reply
  12. 03-25-2016, 11:45 PM #12
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Check for 5 consecutive identical values

    You are welcome. Glad to help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Numbering consecutive identical cells in a column
    By boomcie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2015, 07:54 AM
  2. [SOLVED] Totalling consecutive identical rows out of a huge sheet
    By spursfan323 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2013, 06:53 AM
  3. Excel 2008 : Finding Consecutive Identical Upper Case Letters
    By mikeaq in forum Excel General
    Replies: 17
    Last Post: 05-20-2011, 01:48 AM
  4. Check for seven or more consecutive negative values?
    By Andersth in forum Excel General
    Replies: 6
    Last Post: 09-13-2010, 08:50 AM
  5. Check Userform controls for identical values
    By Newbie_Nick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2010, 01:44 PM
  6. Flaging identical data in 3, 5 and 20 consecutive cells in a column
    By SNarfel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2007, 05:23 PM
  7. [SOLVED] Need to find two consecutive identical cells (in a column).
    By How did I get here? in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-03-2006, 03:15 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1