Hi Everyone
I've read a few forums only online and took pieces here and there to help me with my issue and I've gotten this far...
The issue: I have a extract of expense accounts with roughly this format>
Description; Debit; Credit
The accounts are all printed underneath eachother with totals seperating each one.
Some entries are writebacks of provisions made and so they will have an entry with the same amount on both the debit and credit side. My ideal would be that these amounts are formatted with a strikethrough. I have come as far as getting this right, but the strikethrough formatting then also applies itself to other amounts which have the same value within that account. So for example:
Description; Debit; Credit
Provision 500
Writeback 500
Expense 500
(Sorry can't find the strikethrough here but all those amounts would have a strikethrough instead of just 1:1)
I have made a few helper columns to the side of the data which do the following:
Description; Debit; Credit ;Same Account ID; Debit 1st Iteration of Amount in same Account ID; Credit 1st Iteration of Amount in same Account ID
Provision 500dr 6 6dr
Writeback 500cr 6 6cr
Expense 500dr 6 12dr
The Same Account ID column would have the same number in that column for rows in the same account.
The 1st iteration Debit and Credt columns Identify how many times that amount has appeared in that account multiplied by the account ID number so that a conditional formatting logic should go like this:
Amounts that match on the debit and credit side of the same account (by Account ID column) as well as in the corresponding 1st Iteration Debit and Credit helper columns must have a strikethrough format.
The conditional formatting formalu I have to get the strikethroughs working (but doing more than one on each side) is as follows:
=SUMPRODUCT(($N$1:$N$9999=$N1)*($H$1:$H$9999=$G1)*($G$1:$G$9999=$H1))>0
(N is the Acc ID column, G is Debit and H is Credit)
Thanks guys x)
G
Bookmarks