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?
 
Sort by date Sort by votes
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.


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

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
scubatke said:
Once again, I appreciate your efforts Rick. Using the second method, I'm still not quite there.
Click to expand...
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?
 
Upvote 0
scubatke said:
I tried posting the data as a 'quick reply' instead of 'reply with quote' but it went to moderation.
Click to expand...
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.
 
Upvote 0
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")



Marcelo Branco said:
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.
Click to expand...
 
Upvote 0
Rick Rothstein said:
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.
Click to expand...
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...
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
 
Upvote 0
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)?


Rick Rothstein said:
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...
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
Click to expand...
 
Upvote 0
scubatke said:
Thanks Marcelo. My data is in columns AK thru BH.
Click to expand...

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.
 
Last edited:
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
255
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,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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