Adding a value to a customer ID?

Hello!
I have a case number for a patient. 2009-001, for example, in C2.
I also have a contact number for the patient's family member, in D2.
How can I combine the patient's case number 2009-001 from C2 with an entry 
in D2, 2009-001-01for each contact? 

Example:
Jane Doe is case number 2009-001. She has two children, Josh and Jolene.
Josh would be contact number 2009-001-01. Jolene would be contact number 
2009-001-02.
Then we'd have another case number: Doctor Pepper, 2010-003.
She has three contacts: Coke, 2010-003-01, Sprite, 2010-003-02, and Jack 
Daniels, 2010-003-03.

How can I add the "-01", "-02" and "-03" automatically?
Thanks
0
Utf
4/14/2010 3:55:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
918 Views

Similar Articles

[PageSpeed] 50

Not too clear how you data is arranged. This is what I used
FIRST	LAST	ID	           CONTACT	ID for contact
Jane	Doe	2009-001	Josh	2009-001-001
Jane	Doe	2009-001	Jolene	2009-001-002
Dr	Pepper	2009-003	Coke	2009-003-001
Dr	Pepper	2009-003	Sprite	2009-003-002
Dr	Pepper	2009-003	Fresca	2009-003-003

The formula in E2 next to Josh is 
=C2&"-00"&COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1
Just change the 1001 to some other value if you have more records.
This will work for up to 9 contacts
After that you will get something like 2009-004-0011 rather than the 
required 2009-004-011
Could be fixed if needed (does the Old Women who-lived-in-a-shoe frequent 
your clinic?)
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Anne" <Anne@discussions.microsoft.com> wrote in message 
news:5E8E9CDD-DFD7-41A8-A1F9-2D4EC7DABC43@microsoft.com...
> Hello!
> I have a case number for a patient. 2009-001, for example, in C2.
> I also have a contact number for the patient's family member, in D2.
> How can I combine the patient's case number 2009-001 from C2 with an entry
> in D2, 2009-001-01for each contact?
>
> Example:
> Jane Doe is case number 2009-001. She has two children, Josh and Jolene.
> Josh would be contact number 2009-001-01. Jolene would be contact number
> 2009-001-02.
> Then we'd have another case number: Doctor Pepper, 2010-003.
> She has three contacts: Coke, 2010-003-01, Sprite, 2010-003-02, and Jack
> Daniels, 2010-003-03.
>
> How can I add the "-01", "-02" and "-03" automatically?
> Thanks 

0
Bernard
4/14/2010 5:44:40 PM
Hopefully the Old Woman Who Lived in A Shoe will go to her primary care 
doctor instead!! LOL

Thanks so very much, Bernard!!!!


"Bernard Liengme" wrote:

> Not too clear how you data is arranged. This is what I used
> FIRST	LAST	ID	           CONTACT	ID for contact
> Jane	Doe	2009-001	Josh	2009-001-001
> Jane	Doe	2009-001	Jolene	2009-001-002
> Dr	Pepper	2009-003	Coke	2009-003-001
> Dr	Pepper	2009-003	Sprite	2009-003-002
> Dr	Pepper	2009-003	Fresca	2009-003-003
> 
> The formula in E2 next to Josh is 
> =C2&"-00"&COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1
> Just change the 1001 to some other value if you have more records.
> This will work for up to 9 contacts
> After that you will get something like 2009-004-0011 rather than the 
> required 2009-004-011
> Could be fixed if needed (does the Old Women who-lived-in-a-shoe frequent 
> your clinic?)
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> 
> "Anne" <Anne@discussions.microsoft.com> wrote in message 
> news:5E8E9CDD-DFD7-41A8-A1F9-2D4EC7DABC43@microsoft.com...
> > Hello!
> > I have a case number for a patient. 2009-001, for example, in C2.
> > I also have a contact number for the patient's family member, in D2.
> > How can I combine the patient's case number 2009-001 from C2 with an entry
> > in D2, 2009-001-01for each contact?
> >
> > Example:
> > Jane Doe is case number 2009-001. She has two children, Josh and Jolene.
> > Josh would be contact number 2009-001-01. Jolene would be contact number
> > 2009-001-02.
> > Then we'd have another case number: Doctor Pepper, 2010-003.
> > She has three contacts: Coke, 2010-003-01, Sprite, 2010-003-02, and Jack
> > Daniels, 2010-003-03.
> >
> > How can I add the "-01", "-02" and "-03" automatically?
> > Thanks 
> 
> .
> 
0
Utf
4/14/2010 7:47:01 PM
Bernard - maybe

=C2&"-"&text(COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1,"000")


"Bernard Liengme" wrote:

> Not too clear how you data is arranged. This is what I used
> FIRST	LAST	ID	           CONTACT	ID for contact
> Jane	Doe	2009-001	Josh	2009-001-001
> Jane	Doe	2009-001	Jolene	2009-001-002
> Dr	Pepper	2009-003	Coke	2009-003-001
> Dr	Pepper	2009-003	Sprite	2009-003-002
> Dr	Pepper	2009-003	Fresca	2009-003-003
> 
> The formula in E2 next to Josh is 
> =C2&"-00"&COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1
> Just change the 1001 to some other value if you have more records.
> This will work for up to 9 contacts
> After that you will get something like 2009-004-0011 rather than the 
> required 2009-004-011
> Could be fixed if needed (does the Old Women who-lived-in-a-shoe frequent 
> your clinic?)
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> 
> "Anne" <Anne@discussions.microsoft.com> wrote in message 
> news:5E8E9CDD-DFD7-41A8-A1F9-2D4EC7DABC43@microsoft.com...
> > Hello!
> > I have a case number for a patient. 2009-001, for example, in C2.
> > I also have a contact number for the patient's family member, in D2.
> > How can I combine the patient's case number 2009-001 from C2 with an entry
> > in D2, 2009-001-01for each contact?
> >
> > Example:
> > Jane Doe is case number 2009-001. She has two children, Josh and Jolene.
> > Josh would be contact number 2009-001-01. Jolene would be contact number
> > 2009-001-02.
> > Then we'd have another case number: Doctor Pepper, 2010-003.
> > She has three contacts: Coke, 2010-003-01, Sprite, 2010-003-02, and Jack
> > Daniels, 2010-003-03.
> >
> > How can I add the "-01", "-02" and "-03" automatically?
> > Thanks 
> 
> .
> 
0
Utf
4/14/2010 8:00:02 PM
Thanks Duke - for some reason my brain seldom thinks about TEXT
best wishes
Bernard

"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message 
news:96CDF544-676C-4AD6-AD52-82E920DD83FC@microsoft.com...
> Bernard - maybe
>
> =C2&"-"&text(COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1,"000")
>
>
> "Bernard Liengme" wrote:
>
>> Not too clear how you data is arranged. This is what I used
>> FIRST LAST ID            CONTACT ID for contact
>> Jane Doe 2009-001 Josh 2009-001-001
>> Jane Doe 2009-001 Jolene 2009-001-002
>> Dr Pepper 2009-003 Coke 2009-003-001
>> Dr Pepper 2009-003 Sprite 2009-003-002
>> Dr Pepper 2009-003 Fresca 2009-003-003
>>
>> The formula in E2 next to Josh is
>> =C2&"-00"&COUNTIF(C:C,C2)-COUNTIF(C2:$C$1001,C2)+1
>> Just change the 1001 to some other value if you have more records.
>> This will work for up to 9 contacts
>> After that you will get something like 2009-004-0011 rather than the
>> required 2009-004-011
>> Could be fixed if needed (does the Old Women who-lived-in-a-shoe frequent
>> your clinic?)
>> best wishes
>> -- 
>> Bernard Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>>
>>
>> "Anne" <Anne@discussions.microsoft.com> wrote in message
>> news:5E8E9CDD-DFD7-41A8-A1F9-2D4EC7DABC43@microsoft.com...
>> > Hello!
>> > I have a case number for a patient. 2009-001, for example, in C2.
>> > I also have a contact number for the patient's family member, in D2.
>> > How can I combine the patient's case number 2009-001 from C2 with an 
>> > entry
>> > in D2, 2009-001-01for each contact?
>> >
>> > Example:
>> > Jane Doe is case number 2009-001. She has two children, Josh and 
>> > Jolene.
>> > Josh would be contact number 2009-001-01. Jolene would be contact 
>> > number
>> > 2009-001-02.
>> > Then we'd have another case number: Doctor Pepper, 2010-003.
>> > She has three contacts: Coke, 2010-003-01, Sprite, 2010-003-02, and 
>> > Jack
>> > Daniels, 2010-003-03.
>> >
>> > How can I add the "-01", "-02" and "-03" automatically?
>> > Thanks
>>
>> .
>> 
0
Bernard
4/15/2010 12:43:17 PM
Reply:

Similar Artilces:

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Adding new rows
I have a spreadsheet with 100 rows in, I need to add a row after each current row, is there a quick way to do this or have i got to insert each new row individually? -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=390760 I assume you want to add an empty new row in between the existing rows. One way would be with an extra column Save as "trial" first.......just in case thi...

Hiding OLD customers in list
I have a customer list (tblCustomers) And an Orders table (tblOrders) I want to have an admin area functionality that prunes (hides from display in any list or report) Customers from the list that have not placed an order in X number of days (Admin can enter the number of days in a text box) Solution does not have to be in days or in a text box. Could be several check boxes (30 days, 10 weeks, 2 years...). I am just looking for the simplest solution for us to limit the Customer list by last order date. Any help here will be appreciated. Thanks in advance If it were me, I would have a ...

Need better way to provision AD accounts that are to have mailboxes
The exchange admins in my company have come up with a way for the sys admins to assign the user's mailbox store location. It's based off of last name. For example, if your last name starts with letter A, you go on Storage Group 1, Mailbox Store A, if your last name starts with letter B, you go on Storage Group 1, Mailbox Store B, if your last name starts with letter E, you go on Storage Group 2, Mailbox Store A, etc.... This is, I believe, and administrative nightmare. Is there a better way to provision AD accounts that are to have mailboxes? If it works for you and them, then al...

How can I customize the Filter Drop down on the formatting toolbar
I want to clear everything out of it, except the customized filters I have created. Can i do that? Project > Filter by > More Filters, then select each one, Edit, and remove the checkbox for Show in Menu. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I want to clear everything out of it, except the customized filters I > have created. Can i do that? > I've tried that - it doesn't affect that drop down filter list - whether they are checked or not. "Andrew Lavinsky" wrote: > Project > Filter by > Mo...

T-SQL script to update A/P account value of Vendor Card
Hello: For some reason, I just noticed that a question that I posed over an hour ago still has not posted to the newsgroups. So, I'm going to try to repost, here. I am trying to develop a simple T-SQL script in Fabrikam where I update the A/P account of the vendor card with a different account number from what is presently there. When I try each of the scripts below, SQL returns 0 results. Why? UPDATE PM00200 SET PM00200.PMAPINDX = GL00100.ACTINDX from PM00200 as PM00200 join GL00100 as GL00100 on PM00200.PMAPINDX = GL00100.ACTINDX WHERE PMAPINDX IN( select ACT...

Can't open file: "The .VBP file for this project contains an invalid or corrupt library references ID"
I'm running Microsoft Excel v.X on a Mac with Panther (OS X). An Excel file which I used a lot now cannot be opened - it says "The .VBP file for this project contains an invalid or corrupt library references ID". I have no idea what this is - it's a single file with just 2 sheets in it, and is fairly simple - no links to outside objects, no graphics, etc. Just a single file. What can I do to get it opened?? Microsoft Word can read it, but I'd like to keep working on it in Excel... Thanks in advance! Mike Levin ...

MS CRM 3.0 Customer Case Problem
Dear CRM Experts, I have a problem with MS CRM 3.0: I want to create a web based form in our company website that customers create their Cases (tickets) by it and we (I and my Tech. staff) response to them (Ticketing system). It seems for security reasons we shouldn’t allow CRM browsing outside of our Internal Network (Intranet). Please help me how can present solution for this problem. Best Regards, Mohsen Ahmadi You have a couple of options. There are third-party apps that will let you integrate CRM with your website, but with 3.0 you would probably need to purchase the External C...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Custom Reports Using MS Access?
Ran across a thread where Glen Adams suggested using MS Access to create custom reports instead of Crystal Reports. But Glenn never said where to point Access to obtain the needed information to create the reports. Glenn, if you are watching can you give us some more info on how to use Access to create a report? I'm not Glen... I think you would use ODBC... to connect to the RMS database. Then use Access to connect via ODBC... Marc Wagner www.gmroii.net "Dan" <anonymous@discussions.microsoft.com> wrote in message news:0df801c46ea7$d79b00b0$a601280a@phx.gbl... &g...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Customer (Pole) Display
Hi, I'm having a problem where Pole Display's on one customer site are dying one by one. At first it starts flickering, then within a few days it dies completely. Has anyone else encountered a similar problem? Thanks. This may be an OPOS driver mismatch or wrong Baud Rate issue. Which PD model and OPOS driver are you using? Try to change the baud rate to others value first. "trcboy" <trcboy@discussions.microsoft.com> wrote in message news:A30ECE7B-0AF4-4497-873D-D9900D5F6B4F@microsoft.com... > Hi, > > I'm having a problem where Pole Display's ...

Slightly OT: Online print house for custom greeting/note cards
Howdy, I somehow got "volunteered" (again) to create a few different designs for notecard/greeting cards for a non-profit org. so they can sell them to raise money. I'm looking for a cost effective online source (print house) that can take uploads of my designs. 4 color face & folds etc. I've looked at a few, but was wondering if anyone has any input Tnx, Rob Can't help you - I use a local printer. Sorry. -- JoAnn Paules MVP Microsoft [Publisher] "Rob Giordano (Crash)" <webmaster@siriussystems.invalid> wrote in message news:%233wnw3P1...

scripting adding obj-users and mdb-use-defaults to exchange 5.5 users
I have successfully created a script for creating exchange 5.5 users. I can't however, figure out how to add the assoc-nt-account to the obj-users [pseudo attribute] or set MDB-Use-Defaults to True :-( Can anyone offer advice on how to do this [other than generate a CSV file and use the exchange admin tool to import it]? Thanks Wayne ...

Event ID in Application Log
I am currently running Exchange 2003 SP1 on both my Front-end and Back-end Servers. I am getting the Event ID 7200 on the Front-end Server. The error that I'm getting is "Background thread FDoUpdateCatalog halted on database "First Storage Group\Mailbox Store (FRONT-END)" due to error code 0x80004005. " I am not running any Full-Text Indexing, it has never been installed. I am also not running any type of Virus scan on this server. It is occuring every 30 minutes on the dot. I have researched for the past two days and can very little information on how to re...

Adding time
I want to add time,like a sum. 12:00+12:00+12:00=36:00? I keep getting a basic time like 12:00. Could you please supply a formula. Thank you Hi In the cell with the formula, Format>Cells>Number>Custom [h]:mm Putting the [ ] brackets around h allows it to sum past 24 hours. Regards Roger Govier trucker wrote: > I want to add time,like a sum. > > 12:00+12:00+12:00=36:00? > > I keep getting a basic time like 12:00. Could you please supply a formula. > > Thank you > ...

Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing tables in the CRM database and/or adding complete tables to the CRM database?? I am asking this from the point of view relating to other existing CRM products (saleslogix, frontrange - shh). From what I have played with so far from playing with my SBS2k/MSCRM install is I my need to have a MS developer skillset. Robb, Using the schema manager in the deployment Manager MMC snap-in, you can add fields to the existing tables. However, there is no way to add new tables. Matt "Robb D" <robbd@eproductscons...

Custom Report 03-19-10
There is no way for me to run a report on Buydown Pricing, is there anyone who has a report for this option? If not, can someone write a custom report for it, and how much might something like this run me? I would like it to do something like buydown quantity greater/less/equal and same for buydown price... please contact me at nagarra @ yahoo . com or respond here with your contact info! -- Thank You Vince :) ...

Custom CommandBar gone
Using Access 2003 I created a custom commandbar and I was using it as the menu bar for my main form. But it's not showing up anymore. It's still a choice in the menu bar drop down field of the form's properties. It is not listed in the toolbar list when I right click on the default menu bar and choose to customize. But if I click on the Properties button on another toolbar, the next window has a drop down box for Selected Toolbar. It is still listed as a choice here. I've tried doing a Compact and Repair, but that didn't help anything. Where did it go and can I get it ...

Pivot Table and adding a % column, that is not in original data
Hi, Is it possible to add a column for % calculations when the % column is not in original data? To clarify, my original data is as follows: Produt Sales Returns Date A 5 June B 6 June A 1 July A 1 September B 1 November When I run the pivot table, one of the columns I'm then looking to get is a total % of returns over sales , but I cant see how to include in a Pivot table. I can add it outside of the table, but that has problems ...

customizations import from 3.0 to 4.0
Is is possible to import customizations into a 4.0 system that were exported from a 3.0 install? You will need to upgrade 3.0 implementation to 4.0 by running MS CRM 4.0 setup. Please go through Microsoft Dynamics CRM 4.0 Installing Guide. You can download this guide from folowing URL http://www.microsoft.com/downloads/details.aspx?FamilyId=1CEB5E01-DE9F-48C0-8CE2-51633EBF4714&displaylang=en I don't think that there is any supported way through which you can import 3.0 Customization file to 4.0 implementation. Cheers, Rohit "Leo_B" wrote: > Is is possible to im...

Creating Formula with Embedded Variable ID
I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. I want to copy this formula into multiple tabs within my workbook. My workbook and the external workbook have the same number of and identically named tabs. I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to. I imagine the formula would look something like this: ='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1 I know I can use the following formula to derive th...

custom errors in HRESULT
We have methods that all return HRESULT. We want to also report our custom error values in HRESULT. What's the right way to do this? "Sasha" <Sasha@discussions.microsoft.com> wrote in message news:87F67172-76EA-4B3D-9FEE-EBFC687F83D0@microsoft.com... > We have methods that all return HRESULT. We want to also report our custom > error values in HRESULT. What's the right way to do this? See "Error Handling (COM)" at http://msdn.microsoft.com/library/en-us/com/html/15f3ae3e-1794-4948-a7aa-6309a703364b.asp In particular, read the section on "Usi...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

Excel could not save all the data and formatting you recently added
Hi, One of our users sent me an Excel file of 6 MiB. It has 7 worksheets. Most of them have <100 rows and AH columns, one sheet has 13160 rows and AH columns. The large sheet has autofilter enabled, but no actual filtering is done. (yet) 4 columns have validation: they allow a list of values specified in a range somewhere else in the sheet. There is also conditional formatting. It takes >30 seconds to calculate the sheet, however there are no real calculations, just a few concatenated string. My first impression is that this is yet another example of Excel (ab)used as a database. The p...