Monthly Payment Query

DMG321

New Member
Joined
Jul 30, 2007
Messages
2
Hi folks,

First-time poster but long-term viewer of the Board. Have found it to be an essential source of handy hints and tips but have finally came up with a problem which has left me totally perplexed !

Need to report upon valid payments made + highlight any discrepancies under the following criteria:

Valid Payment:
Four equal payments (of variable amounts for each customer) on four specified dates (identical for all customers) for each customer reference.

For example:
Reference Date Amount
D515 01/07/07 15.99
D515 08/07/07 15.99
D515 15/07/07 15.99
D515 22/07/07 15.99

Invalid Payments
Any payments made outside the specified dates.
Any payments missing on the specified dates.
Any amounts outside the expected four equal payments.

Invalid Payment Example 1:
Reference Date Amount

D515 01/07/07 15.99
D515 08/07/07 01.99
D515 15/07/07 15.99
D515 22/07/07 15.99

Invalid Payment Example 2:
Reference Date Amount

D515 01/07/07 15.99
D515 08/07/07 15.99
D515 22/07/07 15.99
D515 22/07/07 15.99

Invalid Payment Example 3:
Reference Date Amount

D515 01/07/07 15.99
D515 08/07/07 15.99
D515 15/07/07 15.99
D515 22/07/07 15.99
D515 22/07/07 15.99

Invalid Payment Example 4:
Reference Date Amount

D515 01/07/07 15.99
D515 08/07/07 15.99
D515 15/07/07 15.99

I currently identify any discrepancies using a combination of advanced filters and pivot tables but, given the volume of data involved, this process can be quite time-consuming and laborious.

Can anyone suggest a possible method of automating the process ?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
I'm thinking that discrepancies might be more easily detected if the payments for a given reference get flipped up to a single row. I.e., the data would look more like this:

D515 01/07/07 15.99 08/07/07 15.99 15/07/07 15.99 22/07/07 15.99
D516 01/07/07 16.99 08/07/07 16.99 15/07/07 16.99 22/07/07 16.99
D522 01/07/07 14.45 08/07/07 14.45 15/07/07 14.45 22/07/07 14.45

The flipping of the data would need to be done using VBA. I don't know whether you're familiar with it. It would be based on a loop that advance to the next row in column A whenever a new reference number is encountered.

But once you have the data in this form you could write various formulas that do syntax checks on each row. E.g., let's say this data is in columns A to I starting at row 2.

=not(or(c2<>c5, c2<>c7, c2<>c9)) would check that the four payments are equal to each other and that none of them are blank unless all of them are blank.
=b2=01/07/07 would check that the first payment date is correct
=isblank(J2) would check that no more than four payments were in the file for this reference number. Etc. You would look for false values in the formulas in order to find the discrepancies.
 

DMG321

New Member
Joined
Jul 30, 2007
Messages
2
Many thanks for this suggestion, Dan - it's certainly a concept worth pursuing - can see lots of potential there.

Unfortunately, I don't believe my VBA coding skills are sufficiently up to speed at present to 'flip' the data - but I'll do some digging around the forum archives and see if there's anything out there I can adapt to do the job.

Thanks again.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,095
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top