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" <(E-Mail Removed)> wrote in message
news:5E8E9CDD-DFD7-41A8-A1F9-(E-Mail Removed)...
> 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
Similar Threads
