matching positive/negative amounts and highlighted them

[Jocote46]

Good morning, I have a big database with almost 100k records that I need to match the negatives /positives and highlight them so that I can remove them. I would like to have a macro that looks at the owner name (column D) and does the matching of positive/negative amounts and highlights any matches if found. I would like for the code to look at each owner within the database and perform this function. I'm attaching a short sample of how the data looks like. thank you in advance for your help with this, it will save me a lot of time.

[MarvinP]

Hi Jocote,

I think this is a good problem for using Pivot Tables and a Running Total. See the attached where I've done a PT on Sheet4 that might be exactly what you are looking for. Play with Pivot Tables to see if you can get more of what you want to see. The second column is a running total of amounts and I'd be looking at that to see what was still owed.

[Jocote46]

MarvinP, thanks for the suggestion but that will not work. I want to find the matches, highlight them, sort them by color and remove them. I'm not interested in running totals. I want to remove the matching negative/positive amounts within each owner from the database.

[Jocote46]

Can someone help please, I have a database with almost 100k and I really don't want to do this manually. I really appreciate it.

[Jocote46]

Can i get some help please..i didn't think this is that complicated for you guys.

[MarvinP]

OK Jocate,

Try this code on your sheet.

Please Login or Register  to view this content.

[Jocote46]

MarvinP, i tried it but i'm getting an error, when i debug the code , it seems the error is in selection.autofill line. I get an error. may i ask a question. why are you referencing "sheets2"? is that where the output will be? just trying to understand the reason. anyhow, i do appreciate your help but is not working when i try it. Thank you.

[Jocote46]

MarvinP, Ok i see why you are refencing "sheets2" in the code. another questions, I do see some rows highlighted, but I noticed that the second and third owner nothing was highlighted and I do see some +/- amounts that match. could you review it please. do you get an error, when you run the macro?

[MarvinP]

OK Jocote,

Sorry for the confusion. I copied the data to Sheet2 and then recorded a few macros and patched them to work. Use the following code instead on Sheet1

Please Login or Register  to view this content.

Let me explain what I'm doing in the code. The first thing is to build a helper column that has the Tax ID and then the Absolute Value of the Amount. I build this in column AO. Then I sort by Amt from small to large. This puts the negative values at the top. Now when I sort by the helper column, the negative values will be directly above the same amounts.

After this helper and sorting I start at the bottom of the table and march up the Amt column. If the amounts are the same but one neg and the other positive, I delete the two full rows. By the time I get done leaves all things that you want.

Try this new code on Sheet1. I'm sorry I got it wrong the first try.

[Jocote46]

Good morning MarvinP, First of all, thank you for helping with this problem, I really appreciate your time and effort. I have a question, does the macro creates the helper column? I tried running the code and i'm getting an error. it seems the code is trying to do something on AO but stops right there. would you mind, testing the code once again. I'm getting an error 400 when I try to run it. thanks in advance for your help.

[simarui]

Sorry Jocote, I don't see what the issue is... MarvinP's macro seems to be working for me. A quick google search for error 400 suggests you're referencing an object which does not exist (did you delete or rename a worksheet or something?) which line is causing the error? try stepping through the code one line at a time (pressing f8) and let us know what happens.

if you can't figure it out upload your latest workbook with MarvinP's macro so we can try to troubleshoot.

[Jocote46]

Thank you MarvinP and Simarui, one of the problem was that I was trying the code on a different worksheet. I'll try it on another sheet to make sure it works fine. I'll keep you posted.

[Jocote46]

MarvinP and Simarui, I'm uploading a worksheet (test sample2) and code is not working on this one. I debug the code and it seems it finds an error on this line

Selection.AutoFill Destination:=Range("AO2:AO94")

An observation, is it possible to change the tax id and use owner number instead? there are some records that do not have a tax ID and that could create a problem.

Thanks again for your help.

[simarui]

did you maybe click somewhere else on the workbook before you stepped through that line..? the previous line selected ao2 and then that line acts on the selection so if you clicked elsewhere that would cause a problem.

i did a little bit of cleanup on Marvin's code to eliminate some of the selection references... try this one:

Please Login or Register  to view this content.

[Jocote46]

MarvinP and Simarui, I got the code to work, In order for the code to work, I discovered that I have to sort column A in ascending order. when I do this code works fine. If I don't sort then I get error message. thanks both for your time, effort. I do apologize if I'm just barely trying to learn. you guys are awesome!! thanks so much.

[Jocote46]

Simarui, I got the code to work on another worksheet. however, I have to have the worksheet where the macro is in order to work open. in the past I have copied the code to my personal worksheet where I keep my macros but this time it doesn't work. do you know why the macro doesn't work when worksheet is closed?