Counting TYPES of duplicates

I have a dataset that has a good number of duplicates, many of which have more than 2 records.

In these sets of duplicates, there are two fields that have many combinations of values.

I would like to find a way to COUNT THE COMBINATIONS in all of the sets of duplicates, as in, every time there is a type of a certain combination of values, create a count for it:

12345 9 9
12345 9 0

123456 9 9
123456 9 0

(Total: 2) For 9/9 and 9/0

12344567 0 0
12344567 0 0
12344567 0 0

12344567 0 0
12344567 0 0
12344567 0 0

(Total: 2) For 0/0 3x

I have attached a listing of types of combinations that I found by scanning the database, but I don't know what formula or functions would do the trick. I don't think there is something in Subtotal or the Count functionalities that would apply but I will fumble around.

Any help would be appreciated.

Dan B

Tony and Glenn:


1) Glenn: your second question is correct; each record is a duplicate, based on the same ACCIDENT NUMBER (i.e., 12345, 12344567, 123456, etc).

Ok, I admit, I probably should have indicated the duplicates are based on the ACCIDENT Number.

2) I want to count the TYPES of combinations of duplicates. I even thought of making a comma-delimited file to do this but don't know how. For example:

12345, 9,0,9,0 (ACCIDENT_NUM 12345, Type 1 -- 2 sets of 9 killed, 0 injured)
123456, 01,0,1,0,1 (ACCIDENT_NUM 123456, Type 2 --3 sets of 9 killed, 1 injured)

Also:

1234567, 0,1,0,2 (Type 3--ACCIDENT_NUM 1234567, 1 record of 0 killed, 1 injured, 1 record of 0 killed, 2 injured)
12345678, 0,1, 0,1, 0,0 (Type 4--ACCIDENT_NUM 12345678, 2 records 0 killed, 1 injured, 1 record of 0 killed, 0 injured)

and so on, then do Totals for the whole dataset for Type 1 (Total = 2), Type 2 (Total = 1), Type 3 (Total = 1), Type 4 (Total = 1) and so on.

****************

ALright:

I know this wasn't a part of the original post, so if you don't want to answer this, no worries, but:

In addition to counting the TYPES of duplicate scenarios based on ACCIDENT NUM, I also want to:

1) determine the SEVERITY
(IF K > 0 then Severity = K,
IF I > 0 and K, then Severity = I,
IF K and I = 0 then Severity = K
IF K and I = 0, then Severity = PDO)

2) Once SEVERITY has been determined, delete all other records that don't determine Severity.

There is an error in one of the sets of records because I sense that there are 2 sets of duplicates (!) within another??


GRRRRRRRRRRRRRRRR.



I have attached a file that indicates how I ultimately would like things to look.

Tony and Glenn:

Maybe this will work:

How would I:

1) GROUP sets of values together,

2) Create a "Pattern" number (label?) based on the Pattern

3) Do a Count of the Pattern Types in the whole spreadsheet/database?


ACCIDENT NUM KILLED INJURED PATTERN COUNT

12345 0 0
12345 0 0
12345 0 0 1 1

123456 0 0
123456 0 0 2 1

123345 0 0
123345 0 0 2 2

1235456 0 0
1235456 0 0
123456 0 0 1 2

123444 1 1
123444 1 1 3 1

1234445 0 0
1234445 0 0 2 3


How would I create a Pattern Number (PATTERN FIELD)?

In other words, Pattern 1 = 0,0 (3x), Pattern 2 = 0,0 (2x), Pattern 3 = 1,1 (2x), and so on...and Count = SUM of Number of times for Pattern 1, Pattern 2, Pattern 3....

How could I use GROUP to Group the values in KILLED and INJURED and have it equal Pattern 1, Pattern 2, etc...?

(Count is something I'll need but its probably simple enough, I would think, Yes? TOTAL of Patterns in Pattern Field, like =SUM("1",B2:B5, SUM("2",B2:B5), etc.

I could send a database if needbe but the main thing is what is the procedure involved---functions et al.

Dan B

Tony:

DISREGARD the earlier post. Today's post should be more clear as to what I need.

The Accident Num field entries are just RANDOM numbers I threw together to create IDs, they don't have any prefixes or whatever, so don't worry if there are 5 digits in one and 7 digits in another; the main thing is that there are Duplicates--they are groups but the real groups (sequences of values) we want to focus on are the figures in the Killed and Injured fields.

I only used Blank Lines to separate the groups of duplicates; the Master database doesn't have blank lines. Will this be a problem?

This project is for a Google Earth-based application showing the distribution of accidents on a highway network. Please keep this confidential.

Thank you,

Dan

ASK TONY H about why he was asking me?

How anonymous and generic is a "collision database"? I shouldn't have to even mention stuff like that, but if I'm being asked, I'm not running away from it.

Why don't you ask yourself why you have to be concerned?

J:

Thank you big time for saying what you said. The poster who went so far as to ask what this was about was perfectly fine asking but I honestly didn't want to go much further saying what I did, because I knew too well that if I did reveal more specifics it could have led to dangerous curves ahead, such as what the other poster was wondering about as to why I was posting what I did. Maybe there should be a restriction on how far people can go along these lines and whether or not they have the option as to how much they should reveal about their projects.