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

 

[Snakehips]

Your latest example data pated into my sheet as continuous rows, no blanks and so the change I made yielded what looked like a good result.

After posting I tried your original data with the blanks and realised that there was a problem.

I was hoping to find a proper solution before you spotted that you had been a bit premature in breaking out the cold beer!

I reckon another lie down is required.

 

[Rick Rothstein]

See if this array-entered** formula does what you want...

=IF(A2="","",IF(INDIRECT("B"&MAX(IF(A$1:A1<>"",ROW(A$1:A1))))="",MOD(LOOKUP(9E+307,C$1:C1,C$1:C1),8)+1,1))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[SQUIDD]

Tony

I'm getting the beer out regardless, enjoy your lie down.

Dave

 

[SQUIDD]

Rick

Just tested this one, THOROUGHLY.

AMAZING, it works absolutely perfectly.

Great work mate.

Dave

 

[Rick Rothstein]

Rick

Just tested this one, THOROUGHLY.

AMAZING, it works absolutely perfectly.

Great... now you can relax and enjoy your beer!

 

[Snakehips]

See if this array-entered** formula does what you want...

=IF(A2="","",IF(INDIRECT("B"&MAX(IF(A$1:A1<>"",ROW(A$1:A1))))="",MOD(LOOKUP(9E+307,C$1:C1,C$1:C1),8)+1,1))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Bravo maestro!!

As I hate the taste of alcohol so I will celebrate your success with a freshly brewed coffee .

Dave, just out of interest I notice from your many, now shortened, long formulas that it would appear that you are dealing with a large dataset. Certainly column wise.
Are you able to give us an indication of what the devil you are doing with it?

 

[SQUIDD]

Tony

Enjoy that coffee.

What am I doing, I wish I knew. I must tell you now, I am not a gambler.

No

It is a hobby of mine to analyse Greyhound racing data, I built so code that retrieved every all racing results from 2004 until current. It is stacks of data as you can imagine.

Since doing so, excel and VBA has become a bit of a hobby as well.

The last problem you and rick helped me with was a betting sequence.

Column A was if a greyhound was selected to bet on.
Column B was the odds of the greyhound if it won.
and you know column C was the counter.

the counter worked like so.

i had a small table that and i used vlookup.

table is laid out like this.

1 bet value 10
2 bet value 20
3 bet value 30
4 and so on whatever value I set
5
6
7
8

so if the counter was equal to 3, then bet was 30.

Like I said though, it is all just a hobby, unless I spot something amazing of course.(I WOULD NOT FORGET MY EXCEL FRIENDS) LOL.

What is it about excel, sometimes I find myself logging in, just to see if I can help someone else, of course only the easy ones.

It feels nice to help others.

Thanks again guys.

Dave

 

[AliGW]

What is it about excel, sometimes I find myself logging in, just to see if I can help someone else, of course only the easy ones.

It feels nice to help others.

I know the feeling. I learn so much just by hanging out here, and it passes the time whilst Sir is watching the footy.

 

[Snakehips]

So Dave, you're chasing the end of the rainbow?
It's nice to know that I'm contributing to something worthwhile and I look forward to receiving my cut come the big day!!

This formula has served me well down the years for both greyhound and horse racing....

=IF(Legs=4,"Keep your money in your wallet" ,"Pick another animal")

Feel free to use it if you wish.

This forum is great in so many ways.

I dabble for fun and yes there is great satisfaction to be had from actually helping someone. Plus there is always something new to be learned (and sadly, often easily forgotten) from Rick and the like.

Also, it keeps me off the streets and from mugging old ladies.

 

[Rick Rothstein]

This formula has served me well down the years for both greyhound and horse racing....

=IF(Legs=4,"Keep your money in your wallet" ,"Pick another animal")

Feel free to use it if you wish.