I have a data file which comprises multiple events for multiple clients. Each row of the file represents an incident for a client. Many clients have numberous rows of data as he/she has been involved in more than 1 incident. I have sorted the file by client ID and date of incident/event with oldest incident at the bottom of the series for that client and newer incidents higher up in the column. A column for the incident includes a string with an assessment or rating of the incident and there is another column that then records the actions taken (which is a string). I am wanting to count for each client the number of consecutive instances of a specific action being taken (ie "No Action") if the incident has been assigned a specific rating by the asessor of "Rating 2". But I want the count to reset if any of the other actions are taken (ie, reset if "referred other agency", "Investigation" or "Other Outcome"). If the incident is a"Rating 3" and the action code is "No Action" then this should neither reset the count nor does it contribute to the count.
Data I have looks something like this (but without the consecutive count score which I have done manually):
Client_ID DAte Assessment rating Outcome Consecutive Count score
1 3/2/2015 Rating 2 No Action 1
1 19/1/2015 Rating 1 Investigation 0
1 11/1/2015 Rating 2 No Action 1
1 6/1/2015 Rating 2 No Action 2
2 21/3/2015 Rating 2 No Action 1
2 14/3/2015 Rating 3 No Action 1
2 7/3/2015 Rating 2 No Action 2
2 1/3/2015 Rating 2 No Action 3
2 21/2/2015 Rating 3 Referred Other Agency 0
2 14/2/2015 Rating 2 No Action 1
3 4/3/2015 Rating 2 No Action 1
3 27/2/2015 Rating 2 Investigation 0
3 20/2/2015 Rating 2 No Action 1
3 12/2/2015 Rating 3 Other Outcome 0
What would the instruction in Excel be so that I can calculate the consecutive count score.
Data I have looks something like this (but without the consecutive count score which I have done manually):
Client_ID DAte Assessment rating Outcome Consecutive Count score
1 3/2/2015 Rating 2 No Action 1
1 19/1/2015 Rating 1 Investigation 0
1 11/1/2015 Rating 2 No Action 1
1 6/1/2015 Rating 2 No Action 2
2 21/3/2015 Rating 2 No Action 1
2 14/3/2015 Rating 3 No Action 1
2 7/3/2015 Rating 2 No Action 2
2 1/3/2015 Rating 2 No Action 3
2 21/2/2015 Rating 3 Referred Other Agency 0
2 14/2/2015 Rating 2 No Action 1
3 4/3/2015 Rating 2 No Action 1
3 27/2/2015 Rating 2 Investigation 0
3 20/2/2015 Rating 2 No Action 1
3 12/2/2015 Rating 3 Other Outcome 0
What would the instruction in Excel be so that I can calculate the consecutive count score.