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

S

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:
[TABLE="width: 320"]
<tbody>[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]

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

Is this even possible?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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:
Upvote 0
scubatke said:
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:
[TABLE="width: 320"]
<tbody>[TR]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]

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

Is this even possible?
Click to expand...

What is the expected result for

[Table="class: grid"]
[tr][td]
0​
[/td][td]
10​
[/td][td]
0​
[/td][td]
0​
[/td][td]
20​
[/td][/tr]
[/table]

Yes or No?

M.
 
Upvote 0
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.
Click to expand...

M.
 
Upvote 0
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?

Rick Rothstein said:
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.
Click to expand...
 
Upvote 0
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
ABCDEFGHIJK
21101122032No
32103132300No
40010022230Yes
50200000203Yes
61110102030No
71133121001Yes
83101110103No
92331202130No
103210103001Yes
110000002231No
2 or more
Cell Formulas
RangeFormula
K2{=IF(ISNUMBER(FIND("1 1",TRIM(SUBSTITUTE(SUBSTITUTE(CONCAT(IF(A2:J2=0,0,1)),"00"," "),0,"")))),"Yes","No")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
scubatke said:
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?
Click to expand...
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:
Upvote 0
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:
Upvote 0
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.

[TABLE="width: 1492"]
<tbody>[TR]
[TD="align: right"]822.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]119.7[/TD]
[TD="align: right"]61.9[/TD]
[TD="align: right"]31.9[/TD]
[TD="align: right"]61.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]226.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]93.9[/TD]
[TD="align: right"]32.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]229.4[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]49.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]386.3[/TD]
[TD="align: right"]96.2[/TD]
[TD="align: right"]564.4[/TD]
[TD="align: right"]1090.3[/TD]
[TD="align: right"]598.2[/TD]
[TD="align: right"]402.1[/TD]
[TD="align: right"]512.2[/TD]
[TD="align: right"]866.4[/TD]
[TD="align: right"]144.8[/TD]
[TD="align: right"]506.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]267.4[/TD]
[TD="align: right"]184.2[/TD]
[TD="align: right"]242.0[/TD]
[TD="align: right"]384.3[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]1272.7[/TD]
[TD="align: right"]624.2[/TD]
[TD="align: right"]904.3[/TD]
[TD="align: right"]1247.6[/TD]
[TD="align: right"]475.5[/TD]
[TD="align: right"]1170.5[/TD]
[TD="align: right"]690.8[/TD]
[TD="align: right"]1138.5[/TD]
[TD="align: right"]1205.9[/TD]
[TD="align: right"]786.4[/TD]
[TD="align: right"]845.1[/TD]
[TD="align: right"]263.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]1534.2[/TD]
[TD="align: right"]1009.9[/TD]
[TD="align: right"]1197.1[/TD]
[TD="align: right"]1094.6[/TD]
[TD="align: right"]1676.7[/TD]
[TD="align: right"]684.2[/TD]
[TD="align: right"]530.8[/TD]
[TD="align: right"]1277.9[/TD]
[TD="align: right"]938.3[/TD]
[TD="align: right"]1252.6[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]1275[/TD]
[TD="align: right"]1754.1[/TD]
[TD="align: right"]962.8[/TD]
[TD="align: right"]100.5[/TD]
[TD="align: right"]140.1[/TD]
[TD="align: right"]57.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]994.8[/TD]
[TD="align: right"]479.8[/TD]
[TD="align: right"]181.1[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1492"]
<colgroup><col span="19"><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: right"]128.4[/TD]
[TD="align: right"]60.9[/TD]
[TD="align: right"]124.7[/TD]
[TD="align: right"]41.7[/TD]
[TD="align: right"]129.4[/TD]
[TD="align: right"]104.7[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]66.1[/TD]
[TD="align: right"]126.8[/TD]
[TD="align: right"]126.7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]44.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]82.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]144.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]72.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]19.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]263[/TD]
[TD="align: right"]26.3[/TD]
[TD="align: right"]210.4[/TD]
[TD="align: right"]210.4[/TD]
[TD="align: right"]157.8[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]210.4[/TD]
[TD="align: right"]236.7[/TD]
[TD="align: right"]289.3[/TD]
[TD="align: right"]289.3[/TD]
[TD="align: right"]52.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.9[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]55.6[/TD]
[TD="align: right"]325.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]352.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]90.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]202.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]113.7[/TD]
[TD="align: right"]96.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]90.5[/TD]
[TD="align: right"]70.3[/TD]
[TD="align: right"]46.1[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]46.7[/TD]
[TD="align: right"]68.9[/TD]
[TD="align: right"]67.2[/TD]
[TD="align: right"]153.5[/TD]
[TD="align: right"]181.7[/TD]
[TD="align: right"]64.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]85.8[/TD]
[TD="align: right"]68.9[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]198.5[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD="align: right"]196.5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16.6[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17.3[/TD]
[TD="align: right"]0.0[/TD]
[TD="align: right"]0[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]


Rick Rothstein said:
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 .
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Remove blank cell of rows and shift the rest to left
2 3 4
Replies
31
Views
868
mehrana
M
M
  • Solved
Data Validation question
Replies
4
Views
251
Peter_SSs
Peter_SSs
M
  • Question Question
Calculating Lost_Minutes & Lost_Energy
Replies
2
Views
431
maxmin
M
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
508
Mplz
M
B
  • Question Question
Excel 365 Compare Date & Copy Cell Data for Data Base
2 3 4
Replies
33
Views
1K
skillilea
S

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top