Assigning numbers to spreadsheet entries...

[Guest]

OK let me explain...

I have an excel file with 13,000 cancer cases. Each case (row) is identified
by an individual patient's unique ID number and the year/month/day of their
diagnosis. Each patient is listed in the database at least twice (at least 2
cancer diagnoses).

Right now I have the data sorted by patient ID, then by date of diagnosis. I
need to number the cases (in a new column) 1, 2, 3, etc. in the order they
present for each individual patient.

Example:

Patient ID year_diagnosis NEW COLUMN
0001 1981 1
0001 1988 2
0001 2001 3
0002 1985 1
0002 2003 2

Is there any way to automatically do this?

Any help appreciated...thanks!

JG

 

[Guest]

If these are columns A, B and C, and the data begins in row 2:
In C2 enter 1. In C3: =if(a3=a2,c2+1,1). (In words, if this patient is
the same as the patient on the prior row, add one to the patient's case
number. If it's a new patient, start back at 0). Copy this formula through
the remainder of column C. Note, this relies (heavily!) on the sort you've
done. --Bruce

 

[Guest]

bpeltzer,

Worked perfectly, thank you! You just saved me from hours of unecessary work.

JG