Aggregate by ID number and Counting

I have a file with Id numbers of clients. Each of these clients may have 0 to 28 criminal records. The offenses of these clients could have occurred before or after their treatment discharge date. My variables are:

Id, name, discharge date, arrest date.

What I want is to aggregate by ID number and create two new variables crmBefore and crmAfter that counts the number of crimes that occurred prior to the discharge date and after the discharge date. For example, this is what I have now:

Id DisChrDat ArrDate
1 1/1/2008 11/1/2009
1 1/1/2008 5/1/2009
1 1/1/2008 2/1/2007
1 1/1/2008 3/1/2007
2 3/1/2009 10/1/2008
2 3/1/2009 4/6/2008
2 3/1/2009 6/11/2008
2 3/1/2009 5/21/2008
2 3/1/2009 2/12/2010

And this is what I want (the arrest date becomes irrelevant)

Id DisChrDate ArrDate crmBefore CrmAfter
1 1/1/2008 NA 2 2
2 3/1/2009 NA 4 1

I’m weak at VBA and I have been doing this manually which takes a long time. I hope someone can help me.

Thanks

Zeda

You seem to be assuming that the date of the crime is synonymous with the arrest date. That is frequently not the case. I suggest you change your headings to ArrestBefore and ArrestAfter OR a much better alternative would be to get the dates of the crimes.

And VBA wouldn't be required for this.

Does this help?
In H2

Please Login or Register  to view this content.

In I2

Please Login or Register  to view this content.

Both are array formluae so confirm with Ctrl+Shift+Enter NOT just Enter
Drag/Fill Down as required