Monthly Payment Query

[DMG321]

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 ?

 

[Dan Waterloo]

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]

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.