Hello geniuses. I'm back with the same project that has been haunting my office for several weeks. This time, with a new twist. Now my team wants to remove data from an extremely large collection of billing records. The goal is to remove/mark the rows of data that are involved with the FIRST billing of every occurrence, but keep (and tally) all subsequent billing occurrences. The trick? If the first billing was split into two service bills, we need to delete both of those rows.
Example: Patti was billed on June 1st for the SAME service, but they split the service up into two line entry items. We want to remove the first billing (only), leaving only the June 5th bill. Because the first two rows have the same name and date, we can treat these rows as one entry and mark for deletion.
John, however, was billed on May 1st - and we only need to remove the 5/1 row (keeping 7/8 and 8/5).
Kelly was billed for one service on 1/1, but we need to remove the two rows of data from our list.
Patti 6/1/2015 delete
Patti 6/1/2015 delete
Patti 6/5/2015 keep
John 5/1/2015 delete
John 7/8/2015 keep
JOhn 8/5/2015 keep
Kelly 1/1/2015 delete
Kelly 1/1/2015 delete
Kelly 5/1/2015 keep
Kelly 5/4/2015 keep
The goal is to remove every occurrence of the FIRST billing (some of which will be two rows of data, united by a common billing date). I thought I could use a nested IF (AND) statement, but I'm failing to generate the logic to compare the first occurrence against the next row successfully. Then I planned to mark a 3rd column in some fashion so that we could tally the rows for deletion, as well as sorting / deleting those rows.
Any advice would be greatly appreciated!!
Example: Patti was billed on June 1st for the SAME service, but they split the service up into two line entry items. We want to remove the first billing (only), leaving only the June 5th bill. Because the first two rows have the same name and date, we can treat these rows as one entry and mark for deletion.
John, however, was billed on May 1st - and we only need to remove the 5/1 row (keeping 7/8 and 8/5).
Kelly was billed for one service on 1/1, but we need to remove the two rows of data from our list.
Patti 6/1/2015 delete
Patti 6/1/2015 delete
Patti 6/5/2015 keep
John 5/1/2015 delete
John 7/8/2015 keep
JOhn 8/5/2015 keep
Kelly 1/1/2015 delete
Kelly 1/1/2015 delete
Kelly 5/1/2015 keep
Kelly 5/4/2015 keep
The goal is to remove every occurrence of the FIRST billing (some of which will be two rows of data, united by a common billing date). I thought I could use a nested IF (AND) statement, but I'm failing to generate the logic to compare the first occurrence against the next row successfully. Then I planned to mark a 3rd column in some fashion so that we could tally the rows for deletion, as well as sorting / deleting those rows.
Any advice would be greatly appreciated!!
Last edited:
