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