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

scubatke · Mar 17, 2018

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

32

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

Is this even possible?

Rick Rothstein · Mar 17, 2018

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.

Marcelo Branco · Mar 17, 2018

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:

110 0 0 0 32

<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

20

Yes or No?

M.

scubatke · Mar 17, 2018

Yes

Marcelo Branco said:
What is the expected result for

0
10
0
0
20

<tbody>
</tbody>

Yes or No?

M.

Marcelo Branco · Mar 17, 2018

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 · Mar 17, 2018

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.

Peter_SSs · Mar 18, 2018

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

A

B

C

D

E

F

G

H

I

J

K

2

1

0

1

2

0

3

2

No

3

2

1

0

3

1

3

2

3

0

No

4

0

1

0

2

3

0

Yes

5

0

2

0

2

0

3

Yes

6

1

0

1

0

2

0

3

0

No

7

1

3

1

2

1

0

1

Yes

8

3

1

0

1

0

1

0

3

No

9

2

3

1

2

0

2

1

3

0

No

10

3

2

1

0

1

0

3

0

1

Yes

11

0

2

3

1

No

2 or more

Cell Formulas
Range	Formula
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.

Rick Rothstein · Mar 18, 2018

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?

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 .

Marcelo Branco · Mar 18, 2018

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.

scubatke · Mar 18, 2018

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	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	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	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	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	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	No

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 .

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

scubatke

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

scubatke

New Member

Marcelo Branco

MrExcel MVP

scubatke

New Member

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

scubatke

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock