Hello,
I understand if this is mind bending but bear with me:
In MS Excel 2007, I have a good number of sets of Duplicate records of Traffic Collision data I have extracted from a larger database, with these key fields:
ACCIDENT NUM (ID field, in text or General format)
DUP (for Duplicate, indicated by a character, for now its a "?")
OCC_KILLED (in Number format)
OCC_INJURED (in Number format)
SEVERITY (in text format)
There may be 1-2 other fields that will be added on but the main situation is this. Here are some scenarios:
ACCIDENT NUM DUP OCC_KILLED OCC_INJURED SEVERITY
12345 ? 0 0
12345 ? 1 2
Or:
123456 ? 0 1
123456 ? 1 0
Or:
1234567 ? 0 0
1234567 ? 0 2
1234567 ? 0 0
This is the formula for DUP:
=IF(OR(A2=A3,A2=A1),"?","")
I need to determine the Severity of the accident based on this:
If OCC_KILLED > 0 then SEVERITY = F (for Fatal)
IF OCC_INJURED > 0 and > OCC_KILLED then SEVERITY = I (for Injury)
IF OCC_KILLED and OCC_INJURED >= 0 then SEVERITY = F
IF OCC_KILLED and OCC_INJURED = 0 then SEVERITY = PDO (for Property Damage Only)
I have a code already in place for how to create the value for Severity but it DOES NOT account for duplicate records:
(in SEVERITY field):
=IF(A1<>0, "F",IF(B1<>0,"I","PDO")
My problems:
1) How do I go through to determine the most correct record to create the Severity Status
2) How do I determine (and indicate) which records to delete--do I need to add another field to do this with another character (I was thinking "!")
3) Is there a way to do deletion programmatically or is it better in this case to do it manually (my project supervisor is Ok with this but I have to think there is a better way)
I have a sense that the record that I want is the one that determines the SEVERITY value but if there are more records than just 2 then the evaluation criteria is more intense.
Also:
4) Is this something that can be done all in one cell or is this better sone with an array of formulas, thought I have noticed SUMIF or COUNTIF might work but I don't know what formula to use
There are other questions but the above covers the heart of the matter.
Thanks for any and all help or attempts at it.
Dan B
Bookmarks