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

scubatke

New Member
Joined
Mar 17, 2018
Messages
10
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:
11000032

<tbody>
</tbody>

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

Is this even possible?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,964
Office Version
2010
Platform
Windows
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:
[table="width: 500"]
[tr]
	[td]Function NumZeroNum(Rng As Range) As Boolean
  NumZeroNum = Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 *[1-9]"
End Function[/td]
[/tr]
[/table]
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:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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:
11000032

<tbody>
</tbody>

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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

scubatke

New Member
Joined
Mar 17, 2018
Messages
10
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?

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Function NumZeroNum(Rng As Range) As Boolean
  NumZeroNum = Join(Application.Index(Rng.Value, 1, 0)) Like "*[1-9]* 0 *[1-9]"
End Function[/TD]
[/TR]
</tbody>[/TABLE]
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,787
Office Version
365
Platform
Windows
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

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF ;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style=";">No</td></tr></tbody></table><p style="width:5.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">2 or more</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF " ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5 ;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">K2</th><td style="text-align:left">{=IF(<font color="#0000FF">ISNUMBER(<font color="#FF0000">FIND(<font color="#00FF00">"1 1",TRIM(<font color="#800080">SUBSTITUTE(<font color="#008080">SUBSTITUTE(<font color="#FF00FF">CONCAT(<font color="#000080">IF(<font color="#0000FF">A2:J2=0,0,1</font>)</font>),"00"," "</font>),0,""</font>)</font>)</font>)</font>),"Yes","No"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,964
Office Version
2010
Platform
Windows
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:
[table="width: 500"]
[tr]
	[td]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[/td]
[/tr]
[/table]
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:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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:

scubatke

New Member
Joined
Mar 17, 2018
Messages
10
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.30119.761.931.961.600000226.7000093.932.900.0229.40No
49.9000000386.396.2564.41090.3598.2402.1512.2866.4144.8506.40267.4184.2242.0384.3Yes
1272.7624.2904.31247.6475.51170.5690.81138.51205.9786.4845.1263.500000000.00.00No
1534.21009.91197.11094.61676.7684.2530.81277.9938.31252.6106612751754.1962.8100.5140.157.500994.8479.8181.1Yes

<tbody>
</tbody>


128.460.9124.741.7129.4104.710566.1126.8126.70044.10000000.00.00No
82.50144.200000000072.50015.519.5000.00.00No
26326.3210.4210.4157.8263210.4236.7289.3289.352.60002.600007.90.00No
55.6325.80352.90003990.800202.80113.796.200000.00.00No
090.570.346.1155046.768.967.2153.5181.764.80230085.868.91930.0198.50No
196.51700000016.617016.90015.4009.2017.30.00No

<colgroup><col span="19"><col span="2"><col><col></colgroup><tbody>
</tbody>


Sorry, forgot about that. Try this version...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]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[/TD]
[/TR]
</tbody>[/TABLE]
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 .
 

Watch MrExcel Video

Forum statistics

Threads
1,100,191
Messages
5,473,046
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top