I have data consisting of multiple groups shown below. Within each group, I need to identify specific records to keep, and specific ones to remove. I have manually done this in the column Keep or Remove but I want to recreate the last column using a formula.
I want to start from the latest date in each group (last row with date in ascending order) and Keep all the rows (starting from the last row in group and moving up) that sum up to the value in column A from bottom up until the entire value of column A is used up. All other remaining rows would be marked as Remove.
What I'm looking for is something where it checks the value of column B (in the last row with the latest date) against the value in column A in the same row. If the value of B is less than or equal to value of column A , mark row as Keep if not mark as Remove. Now we move up one row with the next latest date, if value of column B plus the sum of all the previous Keep column B's is less than or equal to the value in column A, mark as Keep, if not mark as Remove. Moving on to the next row up, if the value in column B plus the sum of all the previous Keep column B's is less than or equal to the value in column A, mark as Keep, if not mark as Remove.
Essentially what I am looking for is a formula for the last column that can do what I am describing above for the below data set.
See below for an example table:
| Group | A | B | Date | Keep or Remove? |
| 1 | 3 | 2 | 5/2/2022 | Remove |
| 1 | 3 | 2 | 5/2/2022 | Remove |
| 1 | 3 | 2 | 5/4/2022 | Remove |
| 1 | 3 | 2 | 5/4/2022 | Remove |
| 1 | 3 | 1 | 5/4/2022 | Keep |
| 1 | 3 | 2 | 5/11/2022 | Remove |
| 1 | 3 | 2 | 5/17/2022 | Remove |
| 1 | 3 | 2 | 6/1/2022 | Remove |
| 1 | 3 | 2 | 6/7/2022 | Remove |
| 1 | 3 | 2 | 6/13/2022 | Remove |
| 1 | 3 | 2 | 6/20/2022 | Remove |
| 1 | 3 | 2 | 6/24/2022 | Keep |
| 2 | 4 | 1 | 5/17/2022 | Remove |
| 2 | 4 | 1 | 6/1/2022 | Keep |
| 2 | 4 | 1 | 6/7/2022 | Keep |
| 2 | 4 | 4 | 6/13/2022 | Remove |
| 2 | 4 | 1 | 6/20/2022 | Keep |
| 2 | 4 | 1 | 6/24/2022 | Keep |
| 3 | 2 | 1 | 6/1/2022 | Keep |
| 3 | 2 | 1 | 6/7/2022 | Keep |
| 3 | 2 | 4 | 6/13/2022 | Remove |
