Assigning numbers to spreadsheet entries...
 

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

Assigning numbers to spreadsheet entries...
 

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

"J. Gutierrez" wrote:

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

Assigning numbers to spreadsheet entries...
 

bpeltzer,

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

JG

"bpeltzer" wrote:

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

"J. Gutierrez" wrote:

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