i have 20 years of wildlife data that i need to evaluate. i have 3 populations (A, B, C) and 3 status categories (Breeding, Active, Inactive). There are over 150 rows of data. The question is "how many inactive groups from each year were active or breeding the year before?" See table 2 as an example of raw data and the results should show in a table as shown in table 2. Thanks for any help! This is a massive project and this is only one of several questions that i can answer with a short cut in excel.
Table 1.
Table 2.
Table 1.
| 2016 | 2015 | 2014 | 2013 | |
| A | Breeding | Breeding | Active | Inactive |
| B | Inactive | Breeding | Inactive | Active |
| C | Active | Inactive | Breeding | Active |
| C | Breeding | Inactive | Inactive | Active |
Table 2.
| 2016 | 2015 | 2014 | 2013 | |
| A | 0 | 0 | 0 | 0 |
| B | 1 | 0 | 1 | 0 |
| C | 0 | 1 | 1 | 0 |