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

[owellwell]

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?

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Date[/td][td]Client[/td][td]Charge[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#92D050]

1/1/2018​

[/td][td=bgcolor:#92D050]A[/td][td=bgcolor:#92D050]

10​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1/1/2018​

[/td][td]B[/td][td]

20​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1/1/2018​

[/td][td]A[/td][td]

30​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td=bgcolor:#92D050]

1/2/2018​

[/td][td=bgcolor:#92D050]B[/td][td=bgcolor:#92D050]

20​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#92D050]

1/1/2018​

[/td][td=bgcolor:#92D050]A[/td][td=bgcolor:#92D050]

10​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#92D050]

1/2/2018​

[/td][td=bgcolor:#92D050]B[/td][td=bgcolor:#92D050]

20​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet77[/td][/tr][/table]

Conditional formatting formula applied to A2:C7:

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

 

[owellwell]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Charge[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD="bgcolor: #92D050"]

1/1/2018​

[/TD]
[TD="bgcolor: #92D050"]A[/TD]
[TD="bgcolor: #92D050"]

10​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]

1/1/2018​

[/TD]
[TD]B[/TD]
[TD]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]

1/1/2018​

[/TD]
[TD]A[/TD]
[TD]

30​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD="bgcolor: #92D050"]

1/2/2018​

[/TD]
[TD="bgcolor: #92D050"]B[/TD]
[TD="bgcolor: #92D050"]

20​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

[/TD]
[TD="bgcolor: #92D050"]

1/1/2018​

[/TD]
[TD="bgcolor: #92D050"]A[/TD]
[TD="bgcolor: #92D050"]

10​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

[/TD]
[TD="bgcolor: #92D050"]

1/2/2018​

[/TD]
[TD="bgcolor: #92D050"]B[/TD]
[TD="bgcolor: #92D050"]

20​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet77[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[starl]

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