Hi Everyone,
I am looking for help creating a macro that can delete revenue values that occur outside of a date range that is contained in two separate columns. The spreadsheet is around 1100 rows long and around 40 columns wide (i.e. it is displaying 3 years worth of revenue data broken up by month, with 4 columns preceding the revenue figures).
<tbody>
</tbody>
Essentially I have a list of account numbers, which are sometimes repeated up to 5 times. What I need is to eliminate any of the revenue figures that fall outside of the earliest and latest dates in the "contract start" and "contract terminated" columns. I already have a macro that will delete duplicate rows based on the Account # so as long as every Account # shows the same revenue values I can take care of eliminating the double counting of revenue when I sum the columns.
For example, for account # 101 it would delete the revenue for May of 2012 and January of 2013, but keep everything in between. Obviously, the contract dates are defined down to the "day" while the revenue columns only go down to the "month" so if it is possible to round the contract dates to the nearest month and do it that way that's even better.
I apologize for not being able to post a screenshot and needing to paste my data in directly, I am unable to download the proper software.
Please let me know if any other information is needed. I appreciate any and all help with this problem!
EDIT: I forgot to mention that if there is no "contract terminated date" then all of the revenue should be kept the occurs after the contract start date.
I am looking for help creating a macro that can delete revenue values that occur outside of a date range that is contained in two separate columns. The spreadsheet is around 1100 rows long and around 40 columns wide (i.e. it is displaying 3 years worth of revenue data broken up by month, with 4 columns preceding the revenue figures).
| Acct # | Acct Name | Contract Start Date | Contract Terminated Date | May-12 | Jun-12 | Jul-12 | Aug-12 | Sep-12 | Oct-12 | Nov-12 | Dec-12 | Jan-13 |
| 101 | Jones | 6/25/2012 | 9/15/2012 | $29,126 | $68,211 | $54,023 | $24,300 | $19,277 | $25,792 | $14,093 | $37,985 | $30,581 |
| 101 | Johnson | 7/27/2012 | 12/16/2012 | $29,126 | $68,211 | $54,023 | $24,300 | $19,277 | $25,792 | $14,093 | $37,985 | $30,581 |
| 102 | Anderson | 5/12/2012 | $21,523 | $175,701 | $212,386 | $10,447 | $181,497 | $135,403 | $97,814 | $59,091 | $136,890 | |
| 102 | Ricci | 7/27/2012 | 9/15/2012 | $21,523 | $175,701 | $212,386 | $10,447 | $181,497 | $135,403 | $97,814 | $59,091 | $136,890 |
| 102 | Anderson | 7/18/2012 | $21,523 | $175,701 | $212,386 | $10,447 | $181,497 | $135,403 | $97,814 | $59,091 | $136,890 | |
| 103 | Wilson | 8/17/2012 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
| 104 | Manter | 6/25/2012 | $153,448 | $202,275 | $181,336 | $136,715 | $148,503 | $173,063 | $104,421 | $122,000 | $102,813 | |
| 105 | Lukas | 8/3/2012 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | $0 | |
| 106 | Erickson | 9/15/2012 | $88,610 | $92,060 | $267,154 | $53,705 | $285,119 | $273,486 | $106,110 | $154,378 | $366,373 | |
| 107 | Fallon | 5/10/2012 | 7/10/2012 | $187,675 | $98,861 | $311,448 | $170,465 | $189,354 | $353,174 | $254,580 | $186,693 | $231,305 |
| 107 | Kimmel | 6/10/2012 | $187,675 | $98,861 | $311,448 | $170,465 | $189,354 | $353,174 | $254,580 | $186,693 | $231,305 | |
| 107 | Fallon | 8/17/2012 | 12/16/2012 | $187,675 | $98,861 | $311,448 | $170,465 | $189,354 | $353,174 | $254,580 | $186,693 | $231,305 |
| 107 | Fallon | 6/10/2012 | 7/27/2012 | $187,675 | $98,861 | $311,448 | $170,465 | $189,354 | $353,174 | $254,580 | $186,693 | $231,305 |
<tbody>
</tbody>
Essentially I have a list of account numbers, which are sometimes repeated up to 5 times. What I need is to eliminate any of the revenue figures that fall outside of the earliest and latest dates in the "contract start" and "contract terminated" columns. I already have a macro that will delete duplicate rows based on the Account # so as long as every Account # shows the same revenue values I can take care of eliminating the double counting of revenue when I sum the columns.
For example, for account # 101 it would delete the revenue for May of 2012 and January of 2013, but keep everything in between. Obviously, the contract dates are defined down to the "day" while the revenue columns only go down to the "month" so if it is possible to round the contract dates to the nearest month and do it that way that's even better.
I apologize for not being able to post a screenshot and needing to paste my data in directly, I am unable to download the proper software.
Please let me know if any other information is needed. I appreciate any and all help with this problem!
EDIT: I forgot to mention that if there is no "contract terminated date" then all of the revenue should be kept the occurs after the contract start date.
Last edited by a moderator:
