sequence number based on date

denise · 07-25-2006, 03:35 PM

Hi folks,

I'm trying to set up an auto sequence number(col A) based on a date entry
(col B) as in the example below. The sequence number should reset to 0001
each time the date in Col B changes. Is this possible?

Col A Col B
072306-0001 07/23/06
072306-0002 07/23/06
072306-0003 07/23/06
072406-0001 07/24/06

Thanks,
Denise

Ron Coderre · 07-25-2006, 03:45 PM

Try something like this:

For a list of dates, beginning in B2

A2: =TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000")
Copy that formula down as far as you need.

Or....to avoid errors for blank cells.
A2: =IF(ISBLANK(B2),"",TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000"))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"denise" wrote:

> Hi folks,
>
> I'm trying to set up an auto sequence number(col A) based on a date entry
> (col B) as in the example below. The sequence number should reset to 0001
> each time the date in Col B changes. Is this possible?
>
> Col A Col B
> 072306-0001 07/23/06
> 072306-0002 07/23/06
> 072306-0003 07/23/06
> 072406-0001 07/24/06
>
> Thanks,
> Denise

denise · 07-25-2006, 05:55 PM

Ron, this works great! Thanks much!

- Denise

"Ron Coderre" wrote:

> Try something like this:
>
> For a list of dates, beginning in B2
>
> A2: =TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000")
> Copy that formula down as far as you need.
>
> Or....to avoid errors for blank cells.
> A2: =IF(ISBLANK(B2),"",TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000"))
>
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "denise" wrote:
>
> > Hi folks,
> >
> > I'm trying to set up an auto sequence number(col A) based on a date entry
> > (col B) as in the example below. The sequence number should reset to 0001
> > each time the date in Col B changes. Is this possible?
> >
> > Col A Col B
> > 072306-0001 07/23/06
> > 072306-0002 07/23/06
> > 072306-0003 07/23/06
> > 072406-0001 07/24/06
> >
> > Thanks,
> > Denise

**bedepe** · 02-18-2014, 06:12 PM

Hi, I used the formula above for my worksheet, and it worked great until I realised that the sequential numbers are reassigned whenever I sort the content of my table by different columns. The numbers should be unique numbers, they shouldn't change when the order of item changes. Any idea how I can fix this? Thanks so much for your help!
Barbara

**arlu1201** · 02-19-2014, 10:29 AM

Barbara,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.