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

1. ## 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. ## 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. ## Re: Complex conditional formatting for duplicate values per date of service per client

Originally Posted by Oscar Cronquist
[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. ## 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