Smart Analysis Macro

[alexaronson]

I have a spread sheet that has item # in column A Customer in columnB starting on row 3 going to row 28,000+. I have data for these Customers that go from Column C to AL (26 rows in all).

What I want to do is develop a macro that will count the number of times I have consecutive zero points repeating for a given range in a row and place the output in AJ of that row.

I need to adjust the evaluation period or range in the macro from 2 to 8 as I test the data.
For example, if the macro range/evaluation period is 2 cells in the below example.
10 20 30 0 0 0 10 0 0 0 0 0 0 0 0 0 8 9 0 12.

Then the number of repeating evaluation periods would be 10.

If I change the evaluation period to 4 then the return value would be 6.

If I change the evaluation period to 8 then the return value would be 2.

To complicate this further, when the macro starts a new row, it needs to only start the evaluation after the first data point has a value. Meaning if I have empty white cells between the Customer and my first value, the macro will not treat those as zero values.

Any suggestions on how I could start this?

I currently have a solution that is tedious and requires a ton of formulas that is a pain to adjust.

Thanks in advance.


Any suggestions on how I could start this?

Thanks in advance.[/img]

 

[Greg Truby]

With your values in A:T, grouping variable in U. This formula works but does not handle blanks at front end. Will see if I can suss that out...

{=SUM(--(SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COUNT($A1:$T1)-$U1+1))-1,1,U1))=0))}

HTH

 

[alexaronson]

Greg,

Thanks for working on this. I see that you are using an array. I know when I copy the formula i have to hit a sequence of keys before I can use it. Can you remind me what that sequence is?

Thanks

 

[Greg Truby]

Ctrl+Shift+Enter

 

[Greg Truby]

OK - those blanks at the beginning added a 2nd level of fun to this question. But this appears to work for me (same test bed as above).

{=SUM(--(SUBTOTAL(9,OFFSET(A1,0,ROW(INDIRECT("1:"&COUNT(OFFSET(A1:T1,0,(COLUMN(T1)-COLUMN(A1)+1)-COUNT(A1:T1),1,COUNT(A1:T1)))-U1+1))-1+(COLUMN(T1)-COLUMN(A1)+1)-COUNT(A1:T1),1,U1))=0))}

 

[alexaronson]

Hmmm,

I can get the formula to work. I click on the cell, enter the formula in the formula bar, hit Ctrl+Shift+Enter. The output I get is the formula.

Any suggestions?

Thanks for your help so far.

 

[Greg Truby]

I assume you meant to type can't instead of can.

Is the cell formatted as text?

Edit -- you do not copy the braces. I include those to indicate that the formula is an array formula.

 

[alexaronson]

The cell format is General.

 

[Greg Truby]

See my edit - are you including the braces?

 

[alexaronson]

I removed the brackets and changed the cell reference from row 1 to row 3 where the data is. However, I get #REF error.