How to write a macros to compare to columns & match amounts, but this is a bit more tricky

Ok guys so i pull in a report. On the left side its the rebates that AP have applied. On the right side its what AR applied.

Column D is totaled and its sum should match to the sum of column S. If they don't something is off and i have to manually search which amounts are off.

So in sheet 2 i paste Column D for AP side rebates into column A.

Column C and so on, is column S, T, Z and AF.

Columns T Z and AF are basically totaled together to get what you get in the S column. So the way i manually do this in sheet 2 is if Column C does have a combination of totals, i delete whats in column C, and Put whats in columns D, E and F, into rows in Column C. Example


Column C D E F
18,869.23 909.23 803.00 17157.00

turns into:

C
909.23
803.00
17157.00

So this pushes the numbers beneath it down accordingly. If column C & D are equal i just delete whats in the D cells. All this is seen in sheet 3. Then in sheet 4, i sort them my least to greatest and find whats not adding up.

Is there a way to write a macros to do this for me?

Originally Posted by dflak

I am a bit confused. I noticed that Column D is the total of the next three columns under all situations. Will this always be true? If so then why are you checking to see if C = D or is it if C=D then you ignore what comes after on the same row, even if there is data there?

P.S. I also noticed that Column D on the original sheet has some positive and negative balances that cancel each other out (mostly they are consecutive, but not necessarily).

Is there any other "binding" such as an account or invoice number or are you going strictly on amounts which might be coincidentally equal for unrelated APs and ARs?

Column C basically equals the summation of column D, E, and F.

so if columns E and F are blank C will always equal D.

The ones that are positive and negative of equal amounts wash each other out and i just delete those. And, im going strictly on amounts. Basically the amounts on both sides of AP and AR have to be the same, if they're not, that means that AP or AR didnt apply a rebate payment.

So i guess the easiest thing would be just to remove column C completely, and just have columns D E and F transpose down into a row all the way through, and then sorting it least to greatest would get rid of the blank cells, right?

Originally Posted by dflak

I am a bit confused. I noticed that Column D is the total of the next three columns under all situations. Will this always be true? If so then why are you checking to see if C = D or is it if C=D then you ignore what comes after on the same row, even if there is data there?

P.S. I also noticed that Column D on the original sheet has some positive and negative balances that cancel each other out (mostly they are consecutive, but not necessarily).

Is there any other "binding" such as an account or invoice number or are you going strictly on amounts which might be coincidentally equal for unrelated APs and ARs?

Originally Posted by dflak

The objective here is to look for all APs that do not have a matching AR regardless of column. Is that correct?

This is ******* awesome. So i can save this as a template and just copy and paste a new report that i pull in from access onto this and the formulas remain in tact?

Thanks man you're awesome!