Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I check a worksheet for duplicate entries or numbers? | Excel Worksheet Functions | |||
How do I sort hexidecimal numbers in an excel spreadsheet? | Excel Discussion (Misc queries) | |||
numbers not adding up correctly in spreadsheet | Excel Worksheet Functions | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
How do I delete 100 account numbers from a spreadsheet colume wer. | Excel Worksheet Functions |