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

1. ## 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. ## 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.``

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

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

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

Thank you very much!

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

No problem. Glad I could help.

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

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