Adding a value to a customer ID?

[Anne]

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

 

[Bernard Liengme]

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

 

[Anne]

Hopefully the Old Woman Who Lived in A Shoe will go to her primary care
doctor instead!! LOL

Thanks so very much, Bernard!!!!

 

[Duke Carey]

Bernard - maybe

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

 

[Bernard Liengme]

Thanks Duke - for some reason my brain seldom thinks about TEXT
best wishes
Bernard