Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Deleting Data Outside of a Date Range W/ Duplicates

  1. #1

    Deleting Data Outside of a Date Range W/ Duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 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


    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!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    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?

  3. #3
    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.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    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.

  5. #5

    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).
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  7. #7
    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.
    Attached Files Attached Files

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  9. #9
    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.

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,706
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •