Z
Zarlot531
here's a worksheet with the following data:
Amounts received | Amounts applied
|
A/R# | A/R#
_____ |
|
33534 $20.00 | 33534 $20.00
33534 $25.00 | 46364 $40.00
46464 $40.00 | 46463 $39.00
46463 $39.00 | 32344 $48.00
32344 $48.00 | 67544 $59.00
67544 $59.00 | 75654 $29.00
75654 $29.00 | 47677 $40.00
47677 $40.00 |
50000 $50.00
Just in case anyone doesn't know, amounts received in theory should
equal amounts applied for the same day. It doesn't usually, and any
excess is sent to suspense, but it's often useful to see which A/R
numbers don't match up.
Of course, the real data I use often has thousands of lines of this
sort, but I'm using this as just an example of what I'm trying to do.
What I've done so far (along with the help of people in here) is
create a macro that cleans these cluttered sheets, deleting
unnecessary data (the data is not presented this cleanly originally),
and creates a pivot table for me to view discrepancies between what
was applied and what was received.
There's also a suspense report that the excess should go to, but I
haven't incorporated that into my macro yet, since there aren't
usually very many items on that report.
Anyway, the whole point of this message is that I'm wondering if
there's an easier way to do this in the macro world. Obviously,
without VBA, really the only way to find discrepancies like these is
to use PivotTables. especially when dealing with thousands of lines.
But is there a way for example... using the above data...to tell vba
to scan the A/R numbers in the "Amounts received" worksheet, and then,
for each A/R number, to compare each of them to the "Amounts applied"
worksheet to see if it exists? And, if it does not exist, to then
report the A/R number and amount?
I think the answer is going to be "well that's what PivotTables are
for." I have a feeling there is a way simply to do it in VBA, but
that it would take forever.
Let me know if I'm wrong please. Thanks.
Amounts received | Amounts applied
|
A/R# | A/R#
_____ |
|
33534 $20.00 | 33534 $20.00
33534 $25.00 | 46364 $40.00
46464 $40.00 | 46463 $39.00
46463 $39.00 | 32344 $48.00
32344 $48.00 | 67544 $59.00
67544 $59.00 | 75654 $29.00
75654 $29.00 | 47677 $40.00
47677 $40.00 |
50000 $50.00
Just in case anyone doesn't know, amounts received in theory should
equal amounts applied for the same day. It doesn't usually, and any
excess is sent to suspense, but it's often useful to see which A/R
numbers don't match up.
Of course, the real data I use often has thousands of lines of this
sort, but I'm using this as just an example of what I'm trying to do.
What I've done so far (along with the help of people in here) is
create a macro that cleans these cluttered sheets, deleting
unnecessary data (the data is not presented this cleanly originally),
and creates a pivot table for me to view discrepancies between what
was applied and what was received.
There's also a suspense report that the excess should go to, but I
haven't incorporated that into my macro yet, since there aren't
usually very many items on that report.
Anyway, the whole point of this message is that I'm wondering if
there's an easier way to do this in the macro world. Obviously,
without VBA, really the only way to find discrepancies like these is
to use PivotTables. especially when dealing with thousands of lines.
But is there a way for example... using the above data...to tell vba
to scan the A/R numbers in the "Amounts received" worksheet, and then,
for each A/R number, to compare each of them to the "Amounts applied"
worksheet to see if it exists? And, if it does not exist, to then
report the A/R number and amount?
I think the answer is going to be "well that's what PivotTables are
for." I have a feeling there is a way simply to do it in VBA, but
that it would take forever.
Let me know if I'm wrong please. Thanks.