Counting 6 consecutive 0's

[bhenderson]

Hello all,

I am not sure if this is more a function or a programming question, but I have a sample attached where I am trying to count 6 consecutive 0's in each row to help me identify trends. I am hoping for my end result to give me something akin to a pivot table that would have...

ID # Times 6 consecutive 0's counted
1 0
2 2
3 4
4 0


Any ideas for how to get from the sample to the intended result?

I tried using frequency functions but I am not quite getting it yet.

Thank you,
Ben

[rylo]

Ben

some questions

1) How are you intending to determine the range to evaluate, given the are all those trailing zeros.
2) How do you quantify 6 zero in a row strings? Say you have zeros in B-H, does that make 2 strings of 6 zeros (B-G and E-H) or 1? Using your example file, what should be the result for row 24, and how is that result calculated?

rylo

[bhenderson]

Hi Rylo

1) For most records I need the entire date range. If I could have a stop perameter that said if there are 45+ consecutive 0's flag this row that would be great.

2) B-H would be one string - good question. And so I would see 26 separate strings of 6 consecutive 0's. But, going back to Q1 there are also 90+ consecutive 0's at the end so I would love to have it notify me that the row may have issues. (i.e. highlight or put some sort of indicator in a new column)

Does that make sense?

I attached the sample with how I counted row 24

[rylo]

Hi

here goes

Please Login or Register  to view this content.

rylo

[kvsrinivasamurthy]

Pl see attached file Sheet1(2).
If you run macro it counts continous zeros in each row and enters the number in the last column of same row.
Any clarifications welcome.