RIN Data Entry Project--Complex

I need data from an Excel spreadsheet transformed to facilitate data analysis. Specifically, I have data on 1,009 United States major federal agency rules. Each rule has a unique identifier called a RIN # (for example, 0560-AF59). There are multiple observations on each rule, as few as 1 and as many as 26, depending upon how long it took each rule to be produced. Each observation represents a 6 month period (i.e., either the Fall or Spring). Each observation is one row in the data. There are 5,290 observations total for the 1,009 rules. The columns in the existing spreadsheet provide details about the rules, including the name of the agency that produced the rule, characteristics of the rule, deadlines for when rules must pass through different stages, and when rules actually passed through these different stages of development. Details about each rule are updated with each successive observation. So, for example, if a new deadline is placed upon the agency, this will be reflected in one observation but not another. Included in the spreadsheet is information about deadlines on the production of agency rules. Some agencies confront no deadlines when producing a rule. Others can confront 4 to 5 or more. What I need is a dataset of unique agency deadlines and whether or not agencies met these deadlines. The spreadsheet I will provide includes information both about deadlines and about whether or not agencies met these deadlines. I have attached a sample from the original spreadsheet and a document that describes how to identify deadlines and whether the agency met the deadline. The final dataset I would like to produce a dataset where the unit of observation is a deadline. So, there may be multiple observations with the same RIN#. I would like the columns in this dataset to include information about this deadline and rule. Specifically, I would like the columns to include the following information: 1. RIN [1 column] 2. Action [1 column]: this indicates the stage of rulemaking for which the deadline is set, ANPRM, NPRM, Direct Final Rule, Final, Final Rule, or Final Action. 3. Source [1 column]: this should indicate whether the deadline is statutory, judicial, agency-set 4. Description [1 column]: This will include the information in the “description” column for statutory and judicial deadlines. This will be blank for agency-set deadlines. 5. Deadline Date [1 column]: 6. Completion Date [1 column]: The date on which the agency completed the action (ANPRM, NPRM, Direct Final Rule, Final, Final Rule, or Final Action). 7. Days [1 column]: The difference between the deadline and the completion dates (completion date-deadline date). Since agency-set deadlines do not include a day, you can assume that the deadline is the last day of the month for the purposes of calculating the difference between deadline and completion. 8. Publication ID [1 column]: The Publication ID from which the deadline was drawn. 9. Publication year [1 column]: From the Publication ID. 10. Publication season [1 column]: From the Publication ID. The season (spring, fall). 11. Title [1 column]: The title of the rule. 12. Agency [1 column]: The agency producing the rule. 13. Priority [1 column]: 14. Major [1 column]: 15. Unfunded Mandates [1 column]: 16. CFR Citation [1 column]: 17. Legal Authority [1 column]: 18. Regulatory Flexibility Analysis [1 column]: 19. Government Levels Affected [1 column]: 20. Federalism [1 column]: 21. Related RINs [1 column]: 22. Change person [1 column]: coded with a 1 if the person listed under “Name” changes between the deadline and completion date. Coded with a 0 otherwise.