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 ?
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 ?