+ Reply to Thread
Results 1 to 7 of 7

Count equal and adjacent cells

  1. #1
    Registered User
    Join Date
    10-18-2020
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    1

    Count equal and adjacent cells

    Hi everyone.

    I've been struggling with something that I think must be quite simple but I'm overthinking it.

    I need to count how many sequential times a group of conditions repeat. In the example bellow you may see what I'm talking about:

    [table="width: 500"]
    [tr]
    Date Location Type Result
    01/10/2020 1/20 Test Conform
    02/10/2020 2/35 Test Conform
    03/10/2020 2/25 Test Non-conform
    04/10/2020 1/20 Repetition Conform
    05/10/2020 1/25 Test Non-conform
    06/10/2020 1/25 Repetition Non-conform
    07/10/2020 1/25 Test Conform
    08/10/2020 1/20 Test Non-conform
    09/10/2020 1/20 Repetition Non-conform
    10/10/2020 1/20 Test Non-conform
    11/10/2020 1/35 Test Non-conform
    20/10/2020 1/25 Test Non-conform
    20/10/2020 1/20 Test Conform

    I need excel to count how many non-conform tests occur per location but the "Repetition" type don't count, the counting is only if two sequential test are non-conform and if a result, for instance, for location 1/20 is "non-conform", then the next test for this location is "conform" the counting should reset.

    I tried to create a dynamic table for this and it helped me visualize how many sequential values are "non-conform" but I can't seem to figure how to count them only if the are adjacent to each other.

    Please help!!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count equal and adjacent cells

    I do not follow your explanation. Please amend your sheet to show your expected reults (manual calculation)... and try again to explain how you get those results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Count equal and adjacent cells

    On second thoughts... a guess. In F2:
    =IFERROR(INDEX($B$2:$B$14,MATCH(0,INDEX(COUNTIF($F$1:$F1,$B$2:$B$14),0),0)),"")

    and in G2:
    =IF(F2="","",SUMPRODUCT(($B$2:$B$13=F2)*($B$3:$B$14=F2)*($D$2:$D$13="Non-conform")*($D$3:$D$14="Non-conform")))
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Count equal and adjacent cells

    Formula for E2 and then fill down =MAX(IF(ISERROR(FIND(REPT("Non-conform,",ROW($L$1:$L$20000)),TEXTJOIN(",",TRUE,IF($B$1:$B$20000=B2,D2,"")),1)),0,ROW($L$1:$L$2000)))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Count equal and adjacent cells

    OP Shows Excel 2013, which doesn't have TEXTJOIN.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Count equal and adjacent cells

    Thanks. My Mistake.

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Count equal and adjacent cells

    This can be done by adding more columns to your table in Folha1!A1:D14.

    E2: #N/A
    E3: =MATCH(1/(C3="Test"),0/(B$2:B2=B3)/(C$2:C2="Test"))

    Fill E3 down into E4:E14.

    F2: =COUNTIFS(C2,"Test",D2,"Non-conform")
    F3: =IF(COUNTIFS(C3,"Test",D3,"Non-conform"),1+IFERROR(INDEX(F$2:F2,E3),0),0)

    Fill F3 down into F4:F14.

    E2:E14 are the row indices in A2:D14 at which the most recent previous instance for the same location in column B and Test appeared in column C. F2:F14 are the consecutive counts down to that row.

    These produce the following results.

    A
    B
    C
    D
    E
    F
    1
    Date Local Type Result
    2
    2020-10-01 1/20 Test Conform #N/A 0
    3
    2020-10-02 2/35 Test Conform #DIV/0! 0
    4
    2020-10-03 2/25 Test Non-conform #N/A 1
    5
    2020-10-04 1/20 Repetition Conform #DIV/0! 0
    6
    2020-10-05 1/25 Test Non-conform #N/A 1
    7
    2020-10-06 1/25 Repetition Non-conform #DIV/0! 0
    8
    2020-10-07 1/25 Test Conform 5 0
    9
    2020-10-08 1/20 Test Non-conform 1 1
    10
    2020-10-09 1/20 Repetition Non-conform #DIV/0! 0
    11
    2020-10-10 1/20 Test Non-conform 8 2
    12
    2020-10-11 1/35 Test Non-conform #N/A 1
    13
    2020-10-20 1/25 Test Non-conform 7 1
    14
    2020-10-20 1/20 Test Conform 10 0

    Meaning the only consecutive Test/Non-conform entries for the same location are in rows 9 and 11 for location 1/20. You'd need another column of additional formulas to determine maximum consecutive numbers before resets.

+ 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. conditional format equal, adjacent cells
    By Emma Eve in forum Excel General
    Replies: 6
    Last Post: 02-21-2019, 01:26 PM
  2. counting in a data set if adjacent cells are equal
    By ed nott in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-25-2018, 07:55 AM
  3. Replies: 2
    Last Post: 09-11-2017, 03:42 PM
  4. [SOLVED] count number of cells in Column C that have a lesser or equal value to the adjacent cell
    By Geoff Jones in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-18-2013, 11:01 AM
  5. [SOLVED] SUM cells with equal values in an adjacent column.
    By angelopc in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-13-2013, 09:55 AM
  6. Counting adjacent cells(text) to equal one value
    By Calithea in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2012, 04:08 AM
  7. ►[SOLVED] by Richard Buttrey - Message Box Error when Adjacent Cells Equal
    By xsoldoutx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-13-2012, 08:07 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