Increment Account Numbers

Hello,

I'm trying to figure out a way to generate account number and
increment them based on dates.  For example:

    A-Account Number                     B-Date
1  P010110-01                         01/01/10
2  P010110-02                         01/01/10
3  P010210-01                         01/02/10
4  P020310-01                         02/03/10
5  P020410-01                         02/04/10
6  P020410-02                         02/04/10
7  P020410-03                         02/04/10

So in column A the account number is generated by the date entered in
column b.
If the date enter is 02/02/10 than the account number automatically
generated should be P020210-01, but if that date already exists than
the account number should increment to -02 and so forth.



0
rickree (8)
1/5/2011 12:11:46 AM
excel 39879 articles. 2 followers. Follow

3 Replies
548 Views

Similar Articles

[PageSpeed] 42

On Tue, 4 Jan 2011 16:11:46 -0800 (PST), rickree <rickree@gmail.com> wrote:

>Hello,
>
>I'm trying to figure out a way to generate account number and
>increment them based on dates.  For example:
>
>    A-Account Number                     B-Date
>1  P010110-01                         01/01/10
>2  P010110-02                         01/01/10
>3  P010210-01                         01/02/10
>4  P020310-01                         02/03/10
>5  P020410-01                         02/04/10
>6  P020410-02                         02/04/10
>7  P020410-03                         02/04/10
>
>So in column A the account number is generated by the date entered in
>column b.
>If the date enter is 02/02/10 than the account number automatically
>generated should be P020210-01, but if that date already exists than
>the account number should increment to -02 and so forth.
>
>

A1:	=TEXT(B1,"Pmmddyy-")& COUNTIF($B$1:B1,B1)

Fill down as far as needed
0
ron6368 (329)
1/5/2011 12:31:39 AM
Thanks awesome!  that works..


On Jan 4, 4:31=A0pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Tue, 4 Jan 2011 16:11:46 -0800 (PST), rickree <rick...@gmail.com> wrot=
e:
> >Hello,
>
> >I'm trying to figure out a way to generate account number and
> >increment them based on dates. =A0For example:
>
> > =A0 =A0A-Account Number =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 B-Date
> >1 =A0P010110-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 01/01/10
> >2 =A0P010110-02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 01/01/10
> >3 =A0P010210-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 01/02/10
> >4 =A0P020310-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 02/03/10
> >5 =A0P020410-01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 02/04/10
> >6 =A0P020410-02 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 02/04/10
> >7 =A0P020410-03 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 02/04/10
>
> >So in column A the account number is generated by the date entered in
> >column b.
> >If the date enter is 02/02/10 than the account number automatically
> >generated should be P020210-01, but if that date already exists than
> >the account number should increment to -02 and so forth.
>
> A1: =A0 =A0 =3DTEXT(B1,"Pmmddyy-")& COUNTIF($B$1:B1,B1)
>
> Fill down as far as needed- Hide quoted text -
>
> - Show quoted text -

0
rickree (8)
1/5/2011 10:24:38 PM
On Wed, 5 Jan 2011 14:24:38 -0800 (PST), rickree <rickree@gmail.com> wrote:

>Thanks awesome!  that works..

Glad to help.  Thanks for the feedback.
0
ron6368 (329)
1/6/2011 1:03:45 AM
Reply:

Similar Artilces:

Multiple Users receiving same account
This is a newbie question, but I can't think for the life of me. I want to setup 1 email address that will be received by 4 users. Do I set this up as a Group then add the users to that group? What kind of group? I don't want to have to go around and setup a pop3 account on each workstation. I know how to acheive this in Linux, but can't think how to do it in MS. Thank you everyone for helping I really appreciate it. Kevin Exchange versions would be helpful. You could set up a distribution list and add those four users to it. "Kevin" <Kevin@discussions.microso...

autotab to the next cell after entering a character/number
to save on my dataentry time i have to fill in a worksheet with numbers between 1 and 5 - im sick of pushing the tab key to get to the next cell - HELP! is autotab possible? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.593 / Virus Database: 376 - Release Date: 20/02/2004 This might be what you're looking for. http://tinyurl.com/39vzv -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "...

Transfer All Email accounts to new system
Is there a way to transfer all email accounts from one computer to another in Outlook 2003? I have quite a few, and it would take a long time to redo each account by hand ...

Merging Accounts #4
I'm using Money Small Business 2005. For some reason, my business checking account is no longer updating, but a new account has appeared (I didn't set it up) from the same bank and is updating. They are both the same account. I figure if I merge them together, I will be back to "normal". However, this new account that magically appeared shows up as a Personal account so I cannot merge them. Can I change that? I tried deleting both of the accounts in order to make a new one that is correct, but then my Accounts Receivable got really screwed up so I restored my file fro...

Cannot open mailboxes of users with account in Exchangeadmins group after they are migrated to Exchange 2003 Server
We have a Windows 2000 SP4 server running Exchange 2000 SP3 with most of our users' mailboxes on it. We have recently built a new Exchange 2003 cluster running on Windows 2003 SP1. I have only migrated the IT department's mailboxes on to it for the time being. One thing I have noticed, is that in the past I used an email account that was a member of the Exchangeadmins group to open user's mailboxes when necessary. This no longer works on users who are now on the Exchange 2003 cluster. When I try and open their mailboxes (through Outlook), I get the error message 'Unable to open...

How can I delete a space/character from a number of fields?
I have a column that has hyphenated numbers (XX-XX-XX-XXX-XXXX) and I need to get rid of the hyphens. They are all in the same format so I think if I could write code to del the 3rd, 6th, 9th, etc characters from a cell, it would work. I just can't seem to figure the code out. or is there an easier way? Thx. Why not just select the all the cells and use Excel's Edit/Replace to replace the hyphens with the empty string (put a hyphen in the "Find what" field and leave the "Replace with" field empty)? -- Rick (MVP - Excel) "BABs" &...

Posting Account Receivable
Hello, I have a client that wants to enter "proposed Invoices" into the GP 8 and be able to change them prior to posting to GL. Is there an easy way to configure this or a 3rd party module which allow this to be accomplished. Invoices are currently imported through a spreadsheet. In a sense, any unposted transaction is in "proposed"state. Have you looked at Quotes in SOP (Sales ORder Processing)? "Chrisi524" <Chrisi524@discussions.microsoft.com> wrote in message news:D8BD71AA-24EA-4CAC-BF5D-09300FE6CDD2@microsoft.com... > Hello, > > I have a...

email accounts logon incoming server
I recently moved my company website to a new provider (server). After satisfactorily migrating the website I attempted to update the email account details in outlook 2002, so our company email accounts would receive from the new mail server. On my own PC running XP, I changed all details but when using 'test account settings' I get an error on 'logon to incoming server'. I know the settings are correct as I have set up the same details on another PC also running XP and using Outlook 2002, and the settings work and test OK. Every time my computer does an automatic send...

Does "Balance this account" EVER work?
Every time I try to balance an account, I get the error where I have to close everything down, start up the salv.exe program, and fix the My Money file. Once I start it back up, I can balance the account. Certainly, this is a royal pain. Is there a permanent fix for this? Any assistance would be greatly appreciated DLB It works here every time. What version are we talking? Just one account or all accounts? Have you read the FAQ article "I think my file has been corrupted. How do I tell? What do I do then?", in the FAQ available at http://www.bollar.org/msmoney/? "DLB&q...

In Excel, how do I change the number of columns that the TAB key .
In Excel, my TAB key sends the cursor from column A to column Z (not to the adjacent cell). How to fix/change this? Tools|Options; open Transition tab and uncheck Transition Navigation Keys Your system is set to mimic Lotus -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "petroMacK" <petroMacK@discussions.microsoft.com> wrote in message news:D3880072-5C76-4DDF-8EBD-4B4369B423D4@microsoft.com... > In Excel, my TAB key sends the cursor from column A to column Z (not to > the > adjacent cell). How to fix/change this? ...

Account deleted
I was contacted about an issue today: 1. Several profiles existed in one view in outlook 2. An account was accidentallydeleted in tools-email accounts 3. Some of the folders disappeared. They were cached for offline access I believe. 4. Assuming the old server is not available for connection, how can I get these back? Thanks! You can't. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, adamtuliper asked: | I was contact...

Renaming user account and accessing old account named mail
Using W2K3 server and Exchange 2K3. I need to rename a users account. First I'd like to know the proper way to do it. Is right clicking and choosing rename the most appropriate and will it change the name in all the necessary places (display, etc). Or should I go to properties and modify the name on the general tab, the modify the name on the Exchange General tab and then on the account tab? Second, once the account has been renamed, where will the e-mail from the prevous account be? Will it show up in the inbox of the new account? Understand - all you are doing in essence is chang...

How to Hide inactive accounts
I can't figure out how to hide my 0 balance, inactive accounts. I'd especially like them NOT to download to my Pocket PC either. Anybody know how to do this? Thanks, Bill In microsoft.public.money, Bill Howell wrote: >I can't figure out how to hide my 0 balance, inactive accounts. I'd >especially like them NOT to download to my Pocket PC either. Anybody know >how to do this? In the Account List, right-click and select account is closed. I don't know how that affects Pocket PC. Thanks Cal, I'll let you know tomorrow whether that affects the PPC syn...

Online update of checking transactions in an investment account
I have a citibank account which is a combination brokerage account and checking account. It is set up in Money as an investment account called "Citigold". It has 1000's of checking transactions and ~10 investment transactions. Now that I've upgraded to Money 2005, I find that Money refuses to download my checking activities to the account since it is not a bank account. Instead it creates two new bank accounts, one labeled "interest checking" and one labeled "brokerage" and posts the appropriate balance to both and the checking transactions to ...

Outlook 2002 will not send and recieve on created account
Outlook 2002 began acting up and it lost one of my e-mail accounts. I noticed that I was not getting any e-mail that I would usually get with the account. I checked and the account is not there. The other thing that I have noticed is that my address book and archives are listed where my other accounts are listed in the area where you would edit or add e-mail accounts. I went in and recreated the missing account and went to send and recieve and it would not bring up the account in the send and recieve box (it brought up all my other accounts). I went back and the account is still...

Project Accounting
Hi, When I run the Cycle Biller, it is automatically computing a state sales tax amount. The project budget for all cost categories are set as non-taxable. Where is the tax schedule defaulting from? Thanks. I believe it is default setup from the customer record under "Ship to" and depends on what sales tax id you have set-up at that screen. "Glen" wrote: > Hi, > > When I run the Cycle Biller, it is automatically computing a state sales tax > amount. > The project budget for all cost categories are set as non-taxable. > > Where is the t...

displaying a single text character for a number value on a subform
I have a form called "clients" that has a combo box field for an e-mail subscription. The choices are unsubscribed (underlying value=0), subscribed (underlying value=1) & not subscribed (underlying value=2). This info also shows on a subform on a form called "companies". I would like for the data onthis "companies" subform to show U for value 0, S for value 1 and N for value 2. I am stupped in how to do this. Thanks for any hints/help Ken Gehle "Ken Gehle" <KenGehle@discussions.microsoft.com> wrote in message news:6E83049A-1308-4E08-9C...

Sorting page numbers
Is there a simple way of sorting pages back into numeric order after building a large spread sheet so they appear in ascending order on the monitor. Without renumbering that is Thanks Mike Hi Mike, Your question is not clear to me, so here are three interpretations even if it is a stretch compared to even what you indicated. If you sorted the rows into a different order and want to restore them you can restore the order if you previous numbered your rows by placing a 1 on a new column (helper column) and used the fill-handle to place 2 into next cell, and 3 into next, etc. If you mean...

Trim String after last number
I have a string that is both text and numeric (ie:A123BC), the last charectors afte the number does not always equal 2 so a right of left formula will not cut it. Besides the Right and left function how can I drop a formula that will always trim after the last numeric value, so that A123BC will return A123. Thanks fo rthe help Bobby Could you provide some more description of the strings. Do they fall within some minimum and maximum length? Do the letter on the end fall within some minimum and maximum length Depending on the complextiy of the data set, this can probably be done with some c...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Money 2007 Deluxe OFX import error on only one account at bank
I have multiple accounts at R1CU and have them all set up for web imports. Today, I added two additional identical-type accounts that have been existing for a month at the R1CU website. The first account, let's call #14, imported transactions just fine. The second account, #15, will not bring anything in. It won't even search for the new account on import or ask me to which account the import should post. It just gives me the "import complete" window and transfers me to my list of web-enabled accounts and their download history. There, the #15 account isn't even lis...

format numbers #2
I have a list of audio books in an excel 2003 spreadsheet. One column should show the duration of the book in this format: 8 hrs 23 mins. But what shows in the column is a 5 digit number, such as 54463 as an example. I cannot figure out how to format this column to translate this number into the hrs and mins of the actual book. Could someone please tell me how to do this? Thanks for your help Joanne Joanne Where does this number come from? It does not seem to relate to 8 hours 23 minutes at all (eg it isn't the total minutes for example or seconds). What is the source of this informa...

Sorting numbered system that relies on decimals
eg. This is the result I am looking for: TS-501.9.8 TS-501.9.9 TS-501.9.10 TS-501.9.11 TS-501.10.1 TS-501.10.2 TS-501.11.1 This is the result I am getting: TS-501.10.1 TS-501.10.2 TS-501.11.1 TS-501.9.10 TS-501.9.11 TS-501.9.8 TS-501.9.9 As you can see the numbers between the dots ascend from 1, however excel takes them as decimals (eg 0.1 = 0.10 = 0.100). Suggestions appreciated! --- Message posted from http://www.ExcelForum.com/ Hi It's possible to do using helping column, and a huge formula in it. But why not to convert your column into form TS-501.09.08 etc. Then you haven'...

automatic receiving of serial numbers
Almost all of our items have serial numbers. One of our stores is actually the central distribution warehouse, and all of our stores are connected via RMS Headquarters. When creating a Transfer Out form from the warehouse we have to select the serial numbers, and would like the serial number data to be sent along with the Transfer In form to the receiving store so that the serial number data does not have to be re-entered at the receiving store. Some technicians at the RMS distributor in Australia told us to unclick the Serial Numbers option "Require Serial Number when Receiving...

Rounding errors in manually entered investment account transaction
I am tracking the performance of the investment subaccount of my life insurance policy in MS Money 2006 Premium. My insurer does not support online updates of investment subaccount performance, so I must enter statement data manually. Shares of investments are only reported with 3 decimal places of accuracy on my statements. As a result, in some cases rounding errors in the number of shares purchased and sold accumulate over months so that after a while the total number of shares that I own as reported by Money differs from the number of shares reported by my insurer. How do I adju...