T
travis
One of my regular jobs is entering payment statements into a
spreadsheet and then matching up the payment against a bank
transaction.
The worksheet "Income" has payments info which includes the following
info and a dozen other fields not relevant to this question:
<InvoiceDate> <BankedDate> <Payer> <Payment>
The worksheet "Bank account" has transactions downloaded from the
bank. A macro of mine translates the narratives into readable english
using a dictionary table.
It includes the following info among its dozen columns:
<BankedDate> <Payment> <Payer> <Reconciled>
Another worksheet parses downloaded payment statements into my format
suitable for the "income" sheet. I have to copy and paste the
downloaded statement into a certain range then a series of SUMIFs and
vlookups turn the raw payment statement into my format used in
"Income".
On that same sheet, I have pivot tables which look at the "Bank
account" sheet and generate something like the following:
BankedDate Yes No Grand Total
21/09/2007 $55.00 $55.00
22/10/2007 $55.01 $55.01
14/11/2007 $58.68 $58.68
19/12/2007 $57.65 $57.65
15/01/2008 $59.11 $59.11
14/02/2008 $54.39 $54.39
13/03/2008 $47.34 $47.34
10/04/2008 $46.89 $46.89
16/05/2008 $46.39 $46.39
16/06/2008 $49.47 $49.47
24/07/2008 $47.30 $47.30
22/08/2008 $44.64 $44.64
22/09/2008 $44.56 $44.56
Grand Total $434.07 $232.36 $666.43
.... where BankedDate is the date the payment was deposited into the
account, Yes and No are the two possible values for the <Reconciled>
field, the numbers are the values of the payments and payer is
actually a filter field, so if I select "Fred" for payer, those are
all of "Fred's" payments.
So, I've just pasted in a payment statement and the value of that
statement is $47.30. Referring to the pivot table I can see that a
$47.30 payment was made on July 24th, so I copy the value 24/7/2008
into my payments list then hit a macro button which copies that
payment to my income sheet.
The next job, currently, is to navigate to the bank account sheet,
scroll to 24/7/08 and manually change the No to Yes. Next time I
visit the statement processing worksheet the pivot table will refresh
and look like this:
BankedDate Yes No Grand Total
21/09/2007 $55.00 $55.00
22/10/2007 $55.01 $55.01
14/11/2007 $58.68 $58.68
19/12/2007 $57.65 $57.65
15/01/2008 $59.11 $59.11
14/02/2008 $54.39 $54.39
13/03/2008 $47.34 $47.34
10/04/2008 $46.89 $46.89
16/05/2008 $46.39 $46.39
16/06/2008 $49.47 $49.47
24/07/2008 $47.30 $47.30
22/08/2008 $44.64 $44.64
22/09/2008 $44.56 $44.56
Grand Total $481.37 $185.06 $666.43
Thus enabling me to see at a glance which payment statements I need to
enter because according to the above I'm missing statements for four
different payments.
So I need a macro that will enable me to automate the process of
finding the <payment> payment made on <BankedDate> by <Payer> and
changing <reconciled> to "Yes".
To make it a little trickier, due to rounding errors the payment is
often slightly different to what it said on the statement. Each
statement has a number of accounts the money is being spread between
and even though I was only paid $47.30, the totals of the payments
being copied to the Income sheet might be $47.34 or something. And to
complicate things further, sometimes <payer> makes more than one
payment a day, so I can't merely tell it to go change the 24/7/08
payment from <Payer>, it has to specifically change the $47.30
payment's reconciled statement while ignoring other payments made on
the same day.
Travis
spreadsheet and then matching up the payment against a bank
transaction.
The worksheet "Income" has payments info which includes the following
info and a dozen other fields not relevant to this question:
<InvoiceDate> <BankedDate> <Payer> <Payment>
The worksheet "Bank account" has transactions downloaded from the
bank. A macro of mine translates the narratives into readable english
using a dictionary table.
It includes the following info among its dozen columns:
<BankedDate> <Payment> <Payer> <Reconciled>
Another worksheet parses downloaded payment statements into my format
suitable for the "income" sheet. I have to copy and paste the
downloaded statement into a certain range then a series of SUMIFs and
vlookups turn the raw payment statement into my format used in
"Income".
On that same sheet, I have pivot tables which look at the "Bank
account" sheet and generate something like the following:
BankedDate Yes No Grand Total
21/09/2007 $55.00 $55.00
22/10/2007 $55.01 $55.01
14/11/2007 $58.68 $58.68
19/12/2007 $57.65 $57.65
15/01/2008 $59.11 $59.11
14/02/2008 $54.39 $54.39
13/03/2008 $47.34 $47.34
10/04/2008 $46.89 $46.89
16/05/2008 $46.39 $46.39
16/06/2008 $49.47 $49.47
24/07/2008 $47.30 $47.30
22/08/2008 $44.64 $44.64
22/09/2008 $44.56 $44.56
Grand Total $434.07 $232.36 $666.43
.... where BankedDate is the date the payment was deposited into the
account, Yes and No are the two possible values for the <Reconciled>
field, the numbers are the values of the payments and payer is
actually a filter field, so if I select "Fred" for payer, those are
all of "Fred's" payments.
So, I've just pasted in a payment statement and the value of that
statement is $47.30. Referring to the pivot table I can see that a
$47.30 payment was made on July 24th, so I copy the value 24/7/2008
into my payments list then hit a macro button which copies that
payment to my income sheet.
The next job, currently, is to navigate to the bank account sheet,
scroll to 24/7/08 and manually change the No to Yes. Next time I
visit the statement processing worksheet the pivot table will refresh
and look like this:
BankedDate Yes No Grand Total
21/09/2007 $55.00 $55.00
22/10/2007 $55.01 $55.01
14/11/2007 $58.68 $58.68
19/12/2007 $57.65 $57.65
15/01/2008 $59.11 $59.11
14/02/2008 $54.39 $54.39
13/03/2008 $47.34 $47.34
10/04/2008 $46.89 $46.89
16/05/2008 $46.39 $46.39
16/06/2008 $49.47 $49.47
24/07/2008 $47.30 $47.30
22/08/2008 $44.64 $44.64
22/09/2008 $44.56 $44.56
Grand Total $481.37 $185.06 $666.43
Thus enabling me to see at a glance which payment statements I need to
enter because according to the above I'm missing statements for four
different payments.
So I need a macro that will enable me to automate the process of
finding the <payment> payment made on <BankedDate> by <Payer> and
changing <reconciled> to "Yes".
To make it a little trickier, due to rounding errors the payment is
often slightly different to what it said on the statement. Each
statement has a number of accounts the money is being spread between
and even though I was only paid $47.30, the totals of the payments
being copied to the Income sheet might be $47.34 or something. And to
complicate things further, sometimes <payer> makes more than one
payment a day, so I can't merely tell it to go change the 24/7/08
payment from <Payer>, it has to specifically change the $47.30
payment's reconciled statement while ignoring other payments made on
the same day.
Travis