+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting: Strikethrough Matching values - but only once!

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Conditional formatting: Strikethrough Matching values - but only once!

    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
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Lokkenreload; 11-15-2013 at 06:36 AM. Reason: Added Screenshot

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    I think it might be doable ... but with a helper column. But you would need to be specific about which transaction types to attempt to match. For example, only include Provision and Writeback, not Expense.

    Suggest you post a sample workbook with some typical data.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-15-2013
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    Hi TMS

    I've added a screenie to the original post.

    The point of the working is sorting out fixed expenses for easy checking.

    Account descriptions do not matter. The only thing that matters is that same value items are struck through but only once. x)

    All formatting from G:I is conditional and aided by the helper columns (M:V).

    In the highlighted region you can see the issue. In the "Admin Fee" account you can see it works perfectly but in the account below you can see that too many strikethroughs occur. Ideally G25 should not be struck through, only G22 and H26. (I also don't want the strikethrough's amounts to get highlighted blue (that format is for repeats of the same amount on the same side (Dr or Cr).

    Column N indicates that for the same number you are working in the same account.
    Column S and T will have the same number if the same amount appears in Column G and H the same amount of times e.g. see cell S22 has the same vale as T26 (i.e. only those must strikethrough because their same value in G:H corresponds to their same value in S:T and they are all in the same account (Col N).

    One more thing. In columns G:H all blanks have been replaced by 0's to aid in the process. (You can see that in the highlighted region)

    Hope all is clear

    G
    Last edited by Lokkenreload; 11-15-2013 at 05:17 AM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    What would you have me do with a "screenie" ... new expression, never heard that before ... type the data in?

    I'm happy to have a look at it for you, no promises, but I've no plans to create a workbook and populate it so that I can test a potential solution.

    Regards, TMS

  5. #5
    Registered User
    Join Date
    11-15-2013
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    Hi TMS

    I've uploaded a edited version of the file to make it small enough : ) (Attached to the OP)

    Thanks for the help

    G

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    OK, I'm struggling ... thought I was there but, no, not quite.

    This uses different helper columns but it's seeing duplicates on the credit and debit sides.

    I'll maybe have another look but, for right now, I need a break from it.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    11-15-2013
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    Hi TMS

    I've just downloaded the sheet you've posted.

    Will have another look at it tomorrow and see if I can finish it.

    Thanks for the help either way xD!

    G

  8. #8
    Registered User
    Join Date
    11-15-2013
    Location
    Cape Town, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Conditional formatting: Strikethrough Matching values - but only once!

    I'm on the verge of completing the worksheet...

    I just need a piece of VBA code that can do the following:

    If a cell has a strikethrough format = 1 otherwise =0

    The cell that is reference could be anywhere on the sheet

    Thank-you

    G

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 03-24-2013, 10:30 AM
  2. Replies: 2
    Last Post: 08-27-2011, 03:05 PM
  3. Excel 2007 : strikethrough conditional formatting
    By HalaK in forum Excel General
    Replies: 10
    Last Post: 08-18-2011, 01:36 PM
  4. Conditional Formatting if Matching Set of Values
    By Niester Rabbit in forum Excel General
    Replies: 10
    Last Post: 06-08-2009, 02:55 AM
  5. matching values that are not in order with conditional formatting
    By TBA in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2006, 11:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1