+ Reply to Thread
Results 1 to 8 of 8

Excel query/macro , delete records where the aggregate value is not over 100

  1. #1
    Registered User
    Join Date
    03-17-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Excel query/macro , delete records where the aggregate value is not over 100

    I have a spreadsheet with about 5000 records. I need to delete out the records where the Amount column is less than $10.00 unless the aggregate of the amount column is greater than or equal to $100. I have an ID column that matches to the name on the record. So is it possible to create something that for each ID number in the ID column if the Amount column is less than $10 delete it unless the same ID appears again and is greater than $100? TIA

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    Yeah, that's possible.

    I plopped some 1's and 2's into A2:A25, and then alternated 9's and 8s in B2:B25.

    SUMIF on the 9's = 108, and 8's = 96.

    Ran the code, and it killed only the 8's.

    Please Login or Register  to view this content.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    03-17-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    Thanks, I ran this but I don't think it deleted the single records that were less than $10.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    You said it shouldn't delete records less than $10 if the total by ID for that record was over $100.

    It'd be considerably easier and much smoother if you slapped together a sample workbook and attached it here.

  5. #5
    Registered User
    Join Date
    03-17-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    ok sorry, after I ran the macro I still have records that are still in the worksheet that are less than $10 but don't add up to atleast $100. I modified it so for my column W is the ID of the person and AH2 is the cost. Maybe I did this part wrong?

    Sub DELSOME()

    For i = Range("W" & Rows.Count).End(xlUp).Row To 2 Step -1

    If Cells(i, 2) < 10 Then

    If WorksheetFunction.SumIf(Range("W2:W4979"), Cells(i, 1), Range("AH2:AH4979")) < 100 Then

    Cells(i, 1).EntireRow.Delete

    End If
    End If
    Next

    End Sub

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    Always be sure to wrap your code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags, or the Mods will be all like "that's bad stuff."
    You can also just select your code and hit the # sign in the toolbar.

    Ahh yes. Cells(i,2) refers to row number, column number. We just need to update that.

    Instead of using 23 which is W, the 23rd Column, we can use strings too.

    Please Login or Register  to view this content.
    Last edited by daffodil11; 03-17-2015 at 02:42 PM.

  7. #7
    Registered User
    Join Date
    03-17-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    4

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    Thank you very much!

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Excel query/macro , delete records where the aggregate value is not over 100

    No problem. Glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro not continuing with other lines of code, when there are no records to delete
    By cbk40060 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2015, 04:09 AM
  2. [SOLVED] Macro - Delete Records based on ID value in Column
    By haleakala17 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-13-2014, 06:18 AM
  3. Aggregate data in query
    By lbernadel in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 11-04-2009, 03:57 PM
  4. Need a macro to select and delete records.
    By jeryst in forum Excel General
    Replies: 1
    Last Post: 01-11-2007, 04:25 PM
  5. [SOLVED] Macro to move info and delete duplicat records
    By Bob Kopp in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-23-2005, 04:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1