Results 1 to 4 of 4

Thread: Complex conditional formatting for duplicate values per date of service per client
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Complex conditional formatting for duplicate values per date of service per client

    Large amount of client data, multiple clients per service date, looking to highlight charges per client, based on duplicate charge entry for that service date. If no duplication, no high lighting of the involved rows, if there are duplicate charges for that service date only then highlight! How might I set this up in conditional formatting?

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Posts
    200
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex conditional formatting for duplicate values per date of service per client

    A
    B
    C
    1
    Date Client Charge
    2
    1/1/2018
    A
    10
    3
    1/1/2018
    B
    20
    4
    1/1/2018
    A
    30
    5
    1/2/2018
    B
    20
    6
    1/1/2018
    A
    10
    7
    1/2/2018
    B
    20
    Sheet: Sheet77

    Conditional formatting formula applied to A2:C7:

    =COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2,$C$2:$C$7,$C2)>1

  3. #3
    New Member
    Join Date
    Aug 2016
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex conditional formatting for duplicate values per date of service per client

    Quote Originally Posted by Oscar Cronquist View Post
    [COLOR=#FFFFFF ]A[/COLOR]
    [COLOR=#FFFFFF ]B[/COLOR]
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]1[/COLOR]
    Date Client Charge
    [COLOR=#FFFFFF ]2[/COLOR]
    1/1/2018
    A
    10
    [COLOR=#FFFFFF ]3[/COLOR]
    1/1/2018
    B
    20
    [COLOR=#FFFFFF ]4[/COLOR]
    1/1/2018
    A
    30
    [COLOR=#FFFFFF ]5[/COLOR]
    1/2/2018
    B
    20
    [COLOR=#FFFFFF ]6[/COLOR]
    1/1/2018
    A
    10
    [COLOR=#FFFFFF ]7[/COLOR]
    1/2/2018
    B
    20
    Sheet: Sheet77

    Conditional formatting formula applied to A2:C7:

    =COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2,$C$2:$C$7,$C2)>1

    Oscar, thank you!

    Your solution did not net me any results, even though your concept could possibly work. The file is complex, over 75K rows, 17 columns. Not all columns are relevant to my issue. The rows are! Rows are grouped by service date and per date by an account name, the account name appear multiple times per date, though with different value, some of these are duplicate and I need to find these duplicate values for the entire service date. I have a file I can send you, it is adjusted for confidentiality of the content, I do not know how to attach it. If you think you can be of further help, I'd forward it to you directly. Let me know. Thank you!

  4. #4
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Complex conditional formatting for duplicate values per date of service per client

    owellwell, attachments (and taking questions off the forum) are not allowed, but there are tools you can use, like Oscar did, to post pieces of your sheet. Please refer to the link (Attachments work around) in my signature to find the tools
    Last edited by starl; Jun 6th, 2018 at 02:38 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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