counter column

[SQUIDD]

Hi

I am trying to create a counter in column C.
Like this the counter counts from 1-8 only if column A has data. It resets if column B has data to 1 and starts over.

	A	B	C	D	E	F	G	H	I
1	2		1
2	3		2
3	6		3
4	1		4
5	4		5
6	1		6
7	4		7
8	2		8
9
10
11	4	3	1
12
13	4	5	1
14	2		2
15	5		3
16	3		4
17
18	6		5
19

<tbody>
</tbody>

Thanks

Dave

 

[Rick Rothstein]

Hi Rick

On my original data set, your formula works perfect, but if i do put in 99 as you did(which could happen) C11 become incorrect, displaying a 1 instead of 2.

On my sample data (which was copy/pasted from your first message), when I put a value in cell A11 (leaving B11 blank), C11 has a 2 in it, not a 1.

 

[Snakehips]

Dave,

I've been watching this with interest. Did not post as Rick just pipped me with a virtually identical formula.

Mine was to have been ... =IF(B2>0,1,IF(A2>0,MOD(LOOKUP(9.99E+307,C$1:C1),8)+1,""))

Are you now saying that continuous values in A will keep generating value 1 to 8, 1 to 8 until interrupted by a bank row. After which, a value in B will re-set to 1?

 

[SQUIDD]

Hi Rick.

Thanks for your patience here, its a hard one to explain.
I too have done exactly as you described, copy and paste into a new sheet, added your formula.
What you said above is correct.
I think I have made a mistake here, when i said it should reset to 1 if data is found in B, it should in-fact have been the row under that got reset to 1.

I will post another table below.

1		1
1		2
1		3
1		4
1	3	5
1		1
1		2
1		3
1	4	4
1		1
1		2
1		3
1		4
1		5
1		6
1		7
1		8
1	2	1
1		1
1		2
1	3	3
1		1

<tbody>
</tbody>

 

[SQUIDD]

Hi Tony

How things.

Just tried your formula, basically same results as rick.
The re-set to 1 is happening 1 row early.

Dave

 

[Snakehips]

Enjoyed yesterday's lie down. Only time will feel if I lay down for long enough

Then try in C2 .....

=IF(B1>0,1,IF(A2>0,MOD(LOOKUP(9.99E+307,C$1:C1),8)+1,""))

 

[SQUIDD]

Hi Tony/ rick

Great stuff, worked right away, you deserve another lie down mate.

You guys are literally genius. Thank you so much. Now I can enjoy the rest of Easter.

I must offer my apologies to Rick, because I obviously did not explain myself properly. Rick has also solved many,many problems for me, and i have never really known him to be wrong.

Have a great Easter both of you and thanks again. Beer from the fridge sounds good.

Dave

 

[Rick Rothstein]

Hi Tony/ rick

Great stuff, worked right away, you deserve another lie down mate.

Are you sure Tony's formula is working correctly for you? I show it placing a 1 in cells C10, C20 and C22 whereas your sample results show them as being blank (the numbers immediately following them are off as well).

 

[SQUIDD]

Tony/Rick

I am back again. I have put the beer back in the fridge.

Tony

I have just tested your formula a bit more thoroughly.

I have 1 problem, if column b has a data in BEFORE a blank row, column c on the blank row (where your formula is) become a 1 and the next populated row becomes 2 when it should be 1.

Maybe i should make some code to filter out the blanks, then copy and past to a new page with formula waiting for the data.
I assume it is the blank rows causing the problems??

Dave

 

[SQUIDD]

Hi Rick

Just read your post, after posting yours, you are correct.

Dave

 

[SQUIDD]

Hi Tony/rick

yes rick is right, it has nothing to do with data in column b as i suggested in my post.
The 1 was always there, i just did not spot it.

Dave