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

[scubatke]

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?

 

[scubatke]

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

I'm trying to post some data but I keep getting a warning from Chrome about suspicious code; I can't even preview the post when it contains the data.

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

Sorry, forgot about that. Try this version...

[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 .

 

[Rick Rothstein]

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

Can you describe (and give an example) in what way you are "still not quite there"? What is my code doing or not doing that you expect it to do?

 

[scubatke]

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

Thanks Peter. I'm using Excel 2013.

 

[scubatke]

Can you describe (and give an example) in what way you are "still not quite there"? What is my code doing or not doing that you expect it to do?

I tried posting the data as a 'quick reply' instead of 'reply with quote' but it went to moderation.

 

[Rick Rothstein]

I tried posting the data as a 'quick reply' instead of 'reply with quote' but it went to moderation.

I don't need your actual data (at least I don't think I do), just a description of what is not happening the way you expect. As for an example, do what you did in your first message.

 

[scubatke]

Thanks Marcelo. My data is in columns AK thru BH. For row 1597 did I set up your formula correctly?

=IF(SUM(IF(FREQUENCY(IF((AL1597:bf1597=0)*(Ak1597:be1597<>0)*(am1597:bg1597=0)+(al1597:bf1597=0)*(ak1597:be1597=0)*(am1597:bg1597<>0),COLUMN(al1597:bf1597)),IF(al1597:bf1597<>0,COLUMN(al1597:bf1597)))>1,1)),"Yes","No")

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]

The data is now posted at the bottom of the first page.

I don't need your actual data (at least I don't think I do), just a description of what is not happening the way you expect. As for an example, do what you did in your first message.

 

[Rick Rothstein]

I don't need your actual data (at least I don't think I do), just a description of what is not happening the way you expect. As for an example, do what you did in your first message.

I may have spotted the problem with my UDF. It appears I omitted an asterisk (wildcard) at the end of the pattern. See if this version of my UDF works for you...

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

 

[scubatke]

PERFECT!!!

I have about 700 rows of data that I had manually flagged previously. Your code pointed out several mistakes that I had made (OK...it was more than several...LOL)!

THANKS SO MUCH!!!

I won't ask you to explain every element of the code but are there any good learning resources you can recommend? What terms should I Google (in addition to User Defined Function)?

I may have spotted the problem with my UDF. It appears I omitted an asterisk (wildcard) at the end of the pattern. See if this version of my UDF works for you...

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

 

[Marcelo Branco]

Thanks Marcelo. My data is in columns AK thru BH.

You already have a solution with VBA - UDF provided by Rick Rothstein - but if you are interested in a solution with formulas, maybe this new and shorter version...

Data sample in post 10 (data in columns AL thru BG; results in column BH)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

AL​

[/TD]
[TD="bgcolor: #DCE6F1"]

AM​

[/TD]
[TD="bgcolor: #DCE6F1"]

AN​

[/TD]
[TD="bgcolor: #DCE6F1"]

AO​

[/TD]
[TD="bgcolor: #DCE6F1"]

AP​

[/TD]
[TD="bgcolor: #DCE6F1"]

AQ​

[/TD]
[TD="bgcolor: #DCE6F1"]

AR​

[/TD]
[TD="bgcolor: #DCE6F1"]

AS​

[/TD]
[TD="bgcolor: #DCE6F1"]

AT​

[/TD]
[TD="bgcolor: #DCE6F1"]

AU​

[/TD]
[TD="bgcolor: #DCE6F1"]

AV​

[/TD]
[TD="bgcolor: #DCE6F1"]

AW​

[/TD]
[TD="bgcolor: #DCE6F1"]

AX​

[/TD]
[TD="bgcolor: #DCE6F1"]

AY​

[/TD]
[TD="bgcolor: #DCE6F1"]

AZ​

[/TD]
[TD="bgcolor: #DCE6F1"]

BA​

[/TD]
[TD="bgcolor: #DCE6F1"]

BB​

[/TD]
[TD="bgcolor: #DCE6F1"]

BC​

[/TD]
[TD="bgcolor: #DCE6F1"]

BD​

[/TD]
[TD="bgcolor: #DCE6F1"]

BE​

[/TD]
[TD="bgcolor: #DCE6F1"]

BF​

[/TD]
[TD="bgcolor: #DCE6F1"]

BG​

[/TD]
[TD="bgcolor: #DCE6F1"]

BH​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

822,3​

[/TD]
[TD]

0​

[/TD]
[TD]

119,7​

[/TD]
[TD]

61,9​

[/TD]
[TD]

31,9​

[/TD]
[TD]

61,6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

226,7​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

93,9​

[/TD]
[TD]

32,9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

229,4​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

49,9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

386,3​

[/TD]
[TD]

96,2​

[/TD]
[TD]

564,4​

[/TD]
[TD]

1090,3​

[/TD]
[TD]

598,2​

[/TD]
[TD]

402,1​

[/TD]
[TD]

512,2​

[/TD]
[TD]

866,4​

[/TD]
[TD]

144,8​

[/TD]
[TD]

506,4​

[/TD]
[TD]

0​

[/TD]
[TD]

267,4​

[/TD]
[TD]

184,2​

[/TD]
[TD]

242​

[/TD]
[TD]

384,3​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

1272,7​

[/TD]
[TD]

624,2​

[/TD]
[TD]

904,3​

[/TD]
[TD]

1247,6​

[/TD]
[TD]

475,5​

[/TD]
[TD]

1170,5​

[/TD]
[TD]

690,8​

[/TD]
[TD]

1138,5​

[/TD]
[TD]

1205,9​

[/TD]
[TD]

786,4​

[/TD]
[TD]

845,1​

[/TD]
[TD]

263,5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

No​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

1534,2​

[/TD]
[TD]

1009,9​

[/TD]
[TD]

1197,1​

[/TD]
[TD]

1094,6​

[/TD]
[TD]

1676,7​

[/TD]
[TD]

684,2​

[/TD]
[TD]

530,8​

[/TD]
[TD]

1277,9​

[/TD]
[TD]

938,3​

[/TD]
[TD]

1252,6​

[/TD]
[TD]

1066​

[/TD]
[TD]

1275​

[/TD]
[TD]

1754,1​

[/TD]
[TD]

962,8​

[/TD]
[TD]

100,5​

[/TD]
[TD]

140,1​

[/TD]
[TD]

57,5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

994,8​

[/TD]
[TD]

479,8​

[/TD]
[TD]

181,1​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

128,4​

[/TD]
[TD]

60,9​

[/TD]
[TD]

124,7​

[/TD]
[TD]

41,7​

[/TD]
[TD]

129,4​

[/TD]
[TD]

104,7​

[/TD]
[TD]

105​

[/TD]
[TD]

66,1​

[/TD]
[TD]

126,8​

[/TD]
[TD]

126,7​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

44,1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

82,5​

[/TD]
[TD]

0​

[/TD]
[TD]

144,2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

72,5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

15,5​

[/TD]
[TD]

19,5​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

263​

[/TD]
[TD]

26,3​

[/TD]
[TD]

210,4​

[/TD]
[TD]

210,4​

[/TD]
[TD]

157,8​

[/TD]
[TD]

263​

[/TD]
[TD]

210,4​

[/TD]
[TD]

236,7​

[/TD]
[TD]

289,3​

[/TD]
[TD]

289,3​

[/TD]
[TD]

52,6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

2,6​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

7,9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

55,6​

[/TD]
[TD]

325,8​

[/TD]
[TD]

0​

[/TD]
[TD]

352,9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

39​

[/TD]
[TD]

90,8​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

202,8​

[/TD]
[TD]

0​

[/TD]
[TD]

113,7​

[/TD]
[TD]

96,2​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

0​

[/TD]
[TD]

90,5​

[/TD]
[TD]

70,3​

[/TD]
[TD]

46,1​

[/TD]
[TD]

155​

[/TD]
[TD]

0​

[/TD]
[TD]

46,7​

[/TD]
[TD]

68,9​

[/TD]
[TD]

67,2​

[/TD]
[TD]

153,5​

[/TD]
[TD]

181,7​

[/TD]
[TD]

64,8​

[/TD]
[TD]

0​

[/TD]
[TD]

23​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

85,8​

[/TD]
[TD]

68,9​

[/TD]
[TD]

193​

[/TD]
[TD]

0​

[/TD]
[TD]

198,5​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

196,5​

[/TD]
[TD]

17​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

16,6​

[/TD]
[TD]

17​

[/TD]
[TD]

0​

[/TD]
[TD]

16,9​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

15,4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

9,2​

[/TD]
[TD]

0​

[/TD]
[TD]

17,3​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

Yes​

[/TD]
[/TR]
</tbody>[/TABLE]


Array formula in BH2 copied down
=IF(SUM(IF(FREQUENCY(IF((AM2:BF2=0)*(AL2:BE2<>0)+(AM2:BF2=0)*(AN2:BG2<>0),COLUMN(AM2:BF2)),IF(AM2:BF2<>0,COLUMN(AM2:BF2)))>1,1)),"Yes","No")
Ctrl+Shift+Enter

M.