I need to assign a sequential number {starting at 1} when there is a date in column B. There are 12,001 rows, but only 350-400 dates in column B {i.e. spread throughout the 12,001 cells in Column B}.
So if column B, has a date in it, I need to assign the next sequential number, if Column B is blank, I do nothing. I have been trying to do this with a "if" formula...but I can't seem to get the sequential number to increase beyond 2.
Can this only be done in VBA? If so, any guideance is greatly appreciated. My VBA skills are weak {actually terrible} as I don't use it very much and I have had hard time mastering the syntax--especially when I need logic as the macro recording does not help with coding of logic. THANKS.
Sequential Number based on Cell Value
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Number based on Cell Value
Will column B only contain dates and blank cells, or can there be other values in between (text, numbers)?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Sequential Number based on Cell Value
Hans,
Sorry I was not specific, yes, your assertion is correct: the cell will either have a date or be blank. Thank you
JimC
Sorry I was not specific, yes, your assertion is correct: the cell will either have a date or be blank. Thank you
JimC
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Number based on Cell Value
That makes it a lot easier - no VBA is needed.
Enter the following formula in C1:
=IF(ISBLANK(B1),"",COUNT(B$1:B1))
Fill down to B12001 (or as far as needed).
Enter the following formula in C1:
=IF(ISBLANK(B1),"",COUNT(B$1:B1))
Fill down to B12001 (or as far as needed).
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 382
- Joined: 08 Feb 2010, 16:08
Re: Sequential Number based on Cell Value
Hans,
You are truly a genious...thank you so much. You have made my day. I need to read about the count function...I was sure this could not be done via a formula. Again, Thank You!
JimC
You are truly a genious...thank you so much. You have made my day. I need to read about the count function...I was sure this could not be done via a formula. Again, Thank You!
JimC
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Number based on Cell Value
COUNT returns the number of cells within the specified range that contain numbers. Since Excel stores dates as numbers, they are picked up by the COUNT function.
You could also use COUNTA here instead of COUNT. COUNTA returns the number of non-blank cells within the specified range.
You could also use COUNTA here instead of COUNT. COUNTA returns the number of non-blank cells within the specified range.
Best wishes,
Hans
Hans