Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

     
    I've found a lot of help from the site previously but this is the first time I've actually asked a question. So thanks for the past help and hopefully thanks in advance for answering this question.

    I have several thousand rows of data (24 columns wide) and I need to flag the rows that have two or more contiguous cells with zeros in them, but only if the cells on each side have data in them.

    For example:
    110 0 0 0 32

    I don't need to flag each instance, just yes/no if it occurs in a row.

    Is this even possible?

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,809
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    You are probably looking for a formula, but until someone comes along and posts one, here is a UDF (user defined function) that you can use (simply pass it the horizontal range you want it to check)...
    Code:
    Function NumZeroNum(Rng As Range) As Boolean NumZeroNum = Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 *[1-9]" End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumZeroNum just like it was a built-in Excel function. For example,

    =NumZeroNum(A1:X1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Last edited by Rick Rothstein; Mar 17th, 2018 at 10:58 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Quote Originally Posted by scubatke View Post
    I've found a lot of help from the site previously but this is the first time I've actually asked a question. So thanks for the past help and hopefully thanks in advance for answering this question.

    I have several thousand rows of data (24 columns wide) and I need to flag the rows that have two or more contiguous cells with zeros in them, but only if the cells on each side have data in them.

    For example:
    110 0 0 0 32

    I don't need to flag each instance, just yes/no if it occurs in a row.

    Is this even possible?
    What is the expected result for

    0
    10
    0
    0
    20


    Yes or No?

    M.

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Yes

    Quote Originally Posted by Marcelo Branco View Post
    What is the expected result for

    0
    10
    0
    0
    20


    Yes or No?

    M.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Rick,

    I think the OP wants Yes for two or more contiguous cells with zeros - i know you can easily adapt your code to achieve this.

    From post 1
    I have several thousand rows of data (24 columns wide) and I need to flag the rows that have two or more contiguous cells with zeros in them, but only if the cells on each side have data in them.
    M.

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Thanks Rick. I'm picking up the instances when there are two cells with zeros; how can I pick up the instances when there are more than two?

    Quote Originally Posted by Rick Rothstein View Post
    You are probably looking for a formula, but until someone comes along and posts one, here is a UDF (user defined function) that you can use (simply pass it the horizontal range you want it to check)...
    Code:
    Function NumZeroNum(Rng As Range) As Boolean NumZeroNum = Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 *[1-9]" End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NumZeroNum just like it was a built-in Excel function. For example,

    =NumZeroNum(A1:X1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,371
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    If you are using Excel 365, you could try this. (Sorry about the formatting but I think the formula copied okay.)

    Array formula so confirm with Ctrl+Shift+Enter, not just Enter. Then copy down

    Excel 2016 (Windows) 32 bit#FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB">#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">
    ABCDEFGHIJK
    21101122032No
    32103132300No
    40010022230Yes
    50200000203Yes
    61110102030No
    71133121001Yes
    83101110103No
    92331202130No
    103210103001Yes
    110000002231No

    #DAE7F5 ;color: #161120">2 or more



    #FFFFFF " >
    Array Formulas#FFFFFF ;border-collapse: collapse; border-color: #BBB">#DAE7F5 ;color: #161120">
    CellFormula
    #DAE7F5 ;color: #161120">K2{=IF(ISNUMBER(FIND("1 1",TRIM(SUBSTITUTE(SUBSTITUTE(CONCAT(IF(A2:J2=0,0,1)),"00"," "),0,"")))),"Yes","No")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by Peter_SSs; Mar 18th, 2018 at 12:35 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,809
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Quote Originally Posted by scubatke View Post
    Thanks Rick. I'm picking up the instances when there are two cells with zeros; how can I pick up the instances when there are more than two?
    Sorry, forgot about that. Try this version...
    Code:
    Function NumZeroNum(Rng As Range) As String NumZeroNum = Array("No", "Yes")(-(Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 0 *[1-9]")) End Function
    Edit Note: If you are looking for a formula solution and you are using Excel 365, make sure you look at what Peter posted in Message #7 .
    Last edited by Rick Rothstein; Mar 18th, 2018 at 12:56 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

    Another possible solution using formulas
    (using the data layout provided by Peter in post 7 above)

    Array formula in K2 copied down
    =IF(SUM(IF(FREQUENCY(IF((B2:I2=0)*(A2:H2<>0)*(C2:J2=0)+(B2:I2=0)*(A2:H2=0)*(C2:J2<>0),COLUMN(B2:I2)),IF(B2:I2<>0,COLUMN(B2:I2)))>1,1)),"Yes","No")
    Ctrl+Shift+Enter

    M.
    Last edited by Marcelo Branco; Mar 18th, 2018 at 01:34 AM.

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to find rows with 2 or more contiguous cells with zeros, but also with data on each side.

      
    Once again, I appreciate your efforts Rick. Using the second method, I'm still not quite there.

    I hope this displays in a legible way...this is part of the actual data. The 2nd and 4th rows correctly returned a 'Yes' but the first row should have as well. The second set of data are all rows that should have been flagged but were missed.

    There is some good news...I'm not seeing any false positives.

    822.3 0 119.7 61.9 31.9 61.6 0 0 0 0 0 226.7 0 0 0 0 93.9 32.9 0 0.0 229.4 0 No
    49.9 0 0 0 0 0 0 386.3 96.2 564.4 1090.3 598.2 402.1 512.2 866.4 144.8 506.4 0 267.4 184.2 242.0 384.3 Yes
    1272.7 624.2 904.3 1247.6 475.5 1170.5 690.8 1138.5 1205.9 786.4 845.1 263.5 0 0 0 0 0 0 0 0.0 0.0 0 No
    1534.2 1009.9 1197.1 1094.6 1676.7 684.2 530.8 1277.9 938.3 1252.6 1066 1275 1754.1 962.8 100.5 140.1 57.5 0 0 994.8 479.8 181.1 Yes


    128.4 60.9 124.7 41.7 129.4 104.7 105 66.1 126.8 126.7 0 0 44.1 0 0 0 0 0 0 0.0 0.0 0 No
    82.5 0 144.2 0 0 0 0 0 0 0 0 0 72.5 0 0 15.5 19.5 0 0 0.0 0.0 0 No
    263 26.3 210.4 210.4 157.8 263 210.4 236.7 289.3 289.3 52.6 0 0 0 2.6 0 0 0 0 7.9 0.0 0 No
    55.6 325.8 0 352.9 0 0 0 39 90.8 0 0 202.8 0 113.7 96.2 0 0 0 0 0.0 0.0 0 No
    0 90.5 70.3 46.1 155 0 46.7 68.9 67.2 153.5 181.7 64.8 0 23 0 0 85.8 68.9 193 0.0 198.5 0 No
    196.5 17 0 0 0 0 0 0 16.6 17 0 16.9 0 0 15.4 0 0 9.2 0 17.3 0.0 0 No


    Quote Originally Posted by Rick Rothstein View Post
    Sorry, forgot about that. Try this version...
    Code:
    Function NumZeroNum(Rng As Range) As String NumZeroNum = Array("No", "Yes")(-(Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 0 *[1-9]")) End Function
    Edit Note: If you are looking for a formula solution and you are using Excel 365, make sure you look at what Peter posted in Message #7 .

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com