Deleting Data Outside of a Date Range W/ Duplicates

rehnwil

New Member
Joined
Jul 5, 2016
Messages
1
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).

[TABLE="width: 1015"]
<tbody>[TR]
[TD]Acct #[/TD]
[TD]Acct Name[/TD]
[TD]Contract Start Date[/TD]
[TD]Contract Terminated Date[/TD]
[TD="align: right"]May-12[/TD]
[TD="align: right"]Jun-12[/TD]
[TD="align: right"]Jul-12[/TD]
[TD="align: right"]Aug-12[/TD]
[TD="align: right"]Sep-12[/TD]
[TD="align: right"]Oct-12[/TD]
[TD="align: right"]Nov-12[/TD]
[TD="align: right"]Dec-12[/TD]
[TD="align: right"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]Jones[/TD]
[TD="align: right"]6/25/2012[/TD]
[TD="align: right"]9/15/2012[/TD]
[TD]$29,126[/TD]
[TD]$68,211[/TD]
[TD]$54,023[/TD]
[TD]$24,300[/TD]
[TD]$19,277[/TD]
[TD]$25,792[/TD]
[TD]$14,093[/TD]
[TD]$37,985[/TD]
[TD]$30,581[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]Johnson[/TD]
[TD="align: right"]7/27/2012[/TD]
[TD="align: right"]12/16/2012[/TD]
[TD]$29,126[/TD]
[TD]$68,211[/TD]
[TD]$54,023[/TD]
[TD]$24,300[/TD]
[TD]$19,277[/TD]
[TD]$25,792[/TD]
[TD]$14,093[/TD]
[TD]$37,985[/TD]
[TD]$30,581[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]Anderson[/TD]
[TD="align: right"]5/12/2012[/TD]
[TD][/TD]
[TD]$21,523[/TD]
[TD]$175,701[/TD]
[TD]$212,386[/TD]
[TD]$10,447[/TD]
[TD]$181,497[/TD]
[TD]$135,403[/TD]
[TD]$97,814[/TD]
[TD]$59,091[/TD]
[TD]$136,890[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]Ricci[/TD]
[TD="align: right"]7/27/2012[/TD]
[TD="align: right"]9/15/2012[/TD]
[TD]$21,523[/TD]
[TD]$175,701[/TD]
[TD]$212,386[/TD]
[TD]$10,447[/TD]
[TD]$181,497[/TD]
[TD]$135,403[/TD]
[TD]$97,814[/TD]
[TD]$59,091[/TD]
[TD]$136,890[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]Anderson[/TD]
[TD="align: right"]7/18/2012[/TD]
[TD][/TD]
[TD]$21,523[/TD]
[TD]$175,701[/TD]
[TD]$212,386[/TD]
[TD]$10,447[/TD]
[TD]$181,497[/TD]
[TD]$135,403[/TD]
[TD]$97,814[/TD]
[TD]$59,091[/TD]
[TD]$136,890[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD]Wilson[/TD]
[TD="align: right"]8/17/2012[/TD]
[TD][/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD]Manter[/TD]
[TD="align: right"]6/25/2012[/TD]
[TD][/TD]
[TD]$153,448[/TD]
[TD]$202,275[/TD]
[TD]$181,336[/TD]
[TD]$136,715[/TD]
[TD]$148,503[/TD]
[TD]$173,063[/TD]
[TD]$104,421[/TD]
[TD]$122,000[/TD]
[TD]$102,813[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD]Lukas[/TD]
[TD="align: right"]8/3/2012[/TD]
[TD][/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD]Erickson[/TD]
[TD="align: right"]9/15/2012[/TD]
[TD][/TD]
[TD]$88,610[/TD]
[TD]$92,060[/TD]
[TD]$267,154[/TD]
[TD]$53,705[/TD]
[TD]$285,119[/TD]
[TD]$273,486[/TD]
[TD]$106,110[/TD]
[TD]$154,378[/TD]
[TD]$366,373[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD]Fallon[/TD]
[TD="align: right"]5/10/2012[/TD]
[TD="align: right"]7/10/2012[/TD]
[TD]$187,675[/TD]
[TD]$98,861[/TD]
[TD]$311,448[/TD]
[TD]$170,465[/TD]
[TD]$189,354[/TD]
[TD]$353,174[/TD]
[TD]$254,580[/TD]
[TD]$186,693[/TD]
[TD]$231,305[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD]Kimmel[/TD]
[TD="align: right"]6/10/2012[/TD]
[TD][/TD]
[TD]$187,675[/TD]
[TD]$98,861[/TD]
[TD]$311,448[/TD]
[TD]$170,465[/TD]
[TD]$189,354[/TD]
[TD]$353,174[/TD]
[TD]$254,580[/TD]
[TD]$186,693[/TD]
[TD]$231,305[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD]Fallon[/TD]
[TD="align: right"]8/17/2012[/TD]
[TD="align: right"]12/16/2012[/TD]
[TD]$187,675[/TD]
[TD]$98,861[/TD]
[TD]$311,448[/TD]
[TD]$170,465[/TD]
[TD]$189,354[/TD]
[TD]$353,174[/TD]
[TD]$254,580[/TD]
[TD]$186,693[/TD]
[TD]$231,305[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD]Fallon[/TD]
[TD="align: right"]6/10/2012[/TD]
[TD="align: right"]7/27/2012[/TD]
[TD]$187,675[/TD]
[TD]$98,861[/TD]
[TD]$311,448[/TD]
[TD]$170,465[/TD]
[TD]$189,354[/TD]
[TD]$353,174[/TD]
[TD]$254,580[/TD]
[TD]$186,693[/TD]
[TD]$231,305[/TD]
[/TR]
</tbody>[/TABLE]

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 fo
rgot 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:

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top