Deleting Data Outside of a Date Range W/ Duplicates

rehnwil

New member
Joined
Jul 6, 2016
Messages
6
Reaction score
0
Points
0
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).

Acct #Acct NameContract Start DateContract Terminated DateMay-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12Jan-13
101Jones6/25/20129/15/2012$29,126$68,211$54,023$24,300$19,277$25,792$14,093$37,985$30,581
101Johnson7/27/201212/16/2012$29,126$68,211$54,023$24,300$19,277$25,792$14,093$37,985$30,581
102Anderson5/12/2012$21,523$175,701$212,386$10,447$181,497$135,403$97,814$59,091$136,890
102Ricci7/27/20129/15/2012$21,523$175,701$212,386$10,447$181,497$135,403$97,814$59,091$136,890
102Anderson7/18/2012$21,523$175,701$212,386$10,447$181,497$135,403$97,814$59,091$136,890
103Wilson8/17/2012$0$0$0$0$0$0$0$0$0
104Manter6/25/2012$153,448$202,275$181,336$136,715$148,503$173,063$104,421$122,000$102,813
105Lukas8/3/2012$0$0$0$0$0$0$0$0$0
106Erickson9/15/2012$88,610$92,060$267,154$53,705$285,119$273,486$106,110$154,378$366,373
107Fallon5/10/20127/10/2012$187,675$98,861$311,448$170,465$189,354$353,174$254,580$186,693$231,305
107Kimmel6/10/2012$187,675$98,861$311,448$170,465$189,354$353,174$254,580$186,693$231,305
107Fallon8/17/201212/16/2012$187,675$98,861$311,448$170,465$189,354$353,174$254,580$186,693$231,305
107Fallon6/10/20127/27/2012$187,675$98,861$311,448$170,465$189,354$353,174$254,580$186,693$231,305


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. Having different account names linked to the same account# should be irrelevent.

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. If there is no "contract terminated date" then all of the revenue should be kept that occurs after the contract start date.

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!
 
Taking only account #102 (3 rows); the earliest date is May 12 2012, and the latest date is September 15 2012. However there are blanks in two of the rows, do we apply there is no "contract terminated date" then all of the revenue should be kept that occurs after the contract start date or use Sept 15th? If Sept 15th is used, do we eliminate data after that date on all 3 rows?
 
In the event that any of the account #s have a blank for contract terminated date then all of the revenue should be kept that occurs after the contract start date for any rows that have that same account #. So in the case of #102 we want all 3 of the rows to show revenue from May 12 2012 on, without a cut off. Or for Acct #107 we would want everything from May 10 2012 and on, without a cut off because one of the 4 rows does not have a terminated date.
 
Making a start… in the meantime, (1) are the dates in the top row real excel dates or just text? If you select a cell, and go into the Format cells dialogue, and choose General on the Number tab, do you see a number in the Sample box or the same text? Pressing Cancel prevents this process from actually making any changes, and (2) while I can see the data appears to be already sorted by Account number, is that true, and if I sort them by Account number and leave them that way, is it OK?

If you could attach a file that would prevent me from making too many guesses about your setup and guessing wrongly. If you can, Go Advanced and Manage Attachments.
 
Data Sample

This is essentially what the raw data looks like (it is pulled from a database universe and so the values are not formatted correctly). I converted the Acct#s to formatted as numbers and changed the Acct Names. The dates are listed by Fiscal Year and the month order in that fiscal year and so are formatted as text. However, I can certainly just retype these in manually as dates that VBA can understand. Hopefully it doesn't hurt that most of the Acct#s I included have zero revenue in this example. And yes, Acct#s can absolutely be sorted as they are in my attachment but when they are pulled from the database initially they are formatted as text (that's why I converted them).
 

Attachments

  • ExcelGuru Public USe.xlsx
    216.3 KB · Views: 15
Well, your file has some challenges.
In the attached could you check a couple of things:
1. The dates in row 4 are a correct translation of the data in row 3.
2. Is the right data lost from body of the table?

Otherwise, will the data always start at row 4?
Can I lose the hundreds of shapes (pictures?) plonked on top of column D (surprised this is raw data pulled from a database)?
The reason I want to sort by column A is only to ensure that all similar acct# are next to each other so that I can process them as blocks of cells.
 

Attachments

  • ExcelGuru6329 Public USe Test.xlsx
    190.4 KB · Views: 15
The dates were actually off by a year, the fiscal year here begins in the calender year previous (apologies, I should have clarified). I have corrected this in the attached.

The data in the body looks fantastic! It is exactly right using the dates that you had.

The data will always start in row 4, yes, and the pictures in column D can be deleted. The spreadsheet is an export from SAP that is pulled from a database so it is SAP that throws it into difficult formatting. I can certainly manually delete the shapes in column D and have the data start in row 1 before running the macro if that simplifies things.
 

Attachments

  • ExcelGuru6329 Public Use Test V2.xlsx
    195.1 KB · Views: 17
Verify this one as before please - as carefully as you can.
Typically 1100 rows you say, my code so far isn't the fastest on the planet so I'm working on it to speed it up.
 

Attachments

  • ExcelGuru6329 Public USe v3.xlsx
    190.6 KB · Views: 18
Everything looks great, I can't find a single mistake!

The spreadsheets typically range anywhere from 500 to 1100 rows, but even if you can't speed the code up it will still be a lifesaver with accuracy and time.
 
In the attached are 2 versions, blah1 and blah2.
blah1 is slower, deletes all the shapes, changes row 4 to real dates but might be easier to tweak than
blah2 which doesn't need to delete shapes, and doesn't need to leave row 4 as real dates.
Both copy sheet1 and work on the copy so leaving sheet1 untouched.

Lot's of assumptions made based on what you have said.

When the book is open you can Alt + F8 to choose which macro to run.
 

Attachments

  • ExcelGuru6329 Public USe.xlsm
    231 KB · Views: 14
It works perfectly! I already went through and manually deleted the images and moved the data to begin in row 4 and so blah2 worked extremely quickly and without error!

The comments you included in the code are very clear and extrmely helpful.

I couldn't ask for a better result. Thank you very much for taking the time to do this, it is greatly appreciated.
 
Back
Top