Need to filter a list of ID's (with multiple entries per ID) which includes end dates for events and a "day count." Ultimately, I need to get the number of ID's (not which IDs, just a count of them) that have exceeded a "day count" (say 220 in a year or 400 in a two year window). Each row is an event with an associated day count and the ID that was involved. Columns below are A through J. "Consecutive Days" is what I want added up per "ID" based on a calendar year from the "Adj End Date" column. I can figure out number of individual ID's later so long as this list gets filtered down to just those exceeding a defined "day count." I'm working on more than 500,000 events in the spreadsheet. Obviously building a query in Access would be better suited but I've got to work on that and its going to take too long.
| ID | Category Code | Purpose Code | Begin Date | Proj End Date | End Date | Adj End Date | Consecutive_Begin_Date | Consecutive_End_Date | Consecutive_Days |
| 102378 | A | O | 16-Jan-17 | 25-Jan-17 | 25-Jan-17 | 25-Jan-17 | 16-Jan-17 | 25-Jan-17 | 9 |
| 102378 | A | O | 15-Mar-17 | 07-Aug-17 | 08-Aug-17 | 08-Aug-17 | 15-Mar-17 | 08-Aug-17 | 146 |
| 102378 | A | A | 29-May-18 | 01-Jun-18 | 01-Jun-18 | 01-Jun-18 | 29-May-18 | 01-Jun-18 | 3 |
| 102378 | C | M | 15-Jun-18 | 29-Jun-18 | 29-Jun-18 | 29-Jun-18 | 15-Jun-18 | 29-Jun-18 | 14 |
| 102378 | C | Z | 06-Mar-19 | 31-Dec-19 | 31-Dec-19 | 06-Mar-19 | 31-Dec-19 | 300 | |
| 302100 | A | A | 18-Oct-18 | 18-May-19 | 28-Apr-19 | 28-Apr-19 | 18-Oct-18 | 28-Apr-19 | 192 |
| 65547 | A | A | 21-Jan-17 | 21-Jul-17 | 20-Jul-17 | 20-Jul-17 | 21-Jan-17 | 20-Jul-17 | 180 |
