Duplicate Value based off two columns

[Ben85]

I am trying to ensure that there is only one entry per day per area.

Column A is the date of entry.
The areas are a list "Anodizing", "Tumbling", "Cleaning", and "Passivation". Which are entered into Column B.

I could pretty easily use a macro to do this, but if I can just use a conditional format or possible data validation to do this it would be preferred as the sheet currently is not macro based.

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> </colgroup><tbody>
[TD="width: 75, align: center"]Date
[/TD]
[TD="width: 117, align: center"]Area
[/TD]

[TD="class: xl65, align: center"]11/1/2018
[/TD]
[TD="align: center"]Anodizing
[/TD]

[TD="class: xl65, align: center"]11/1/2018
[/TD]
[TD="align: center"]Tumbling[/TD]

[TD="class: xl65, align: center"]11/1/2018[/TD]
[TD="align: center"]Cleaning[/TD]

[TD="class: xl65, align: center"] 11/1/2018
[/TD]
[TD="align: center"] Cleaning
[/TD]

[TD="class: xl65, align: center"]10/31/2018
[/TD]
[TD="align: center"]Anodizing
[/TD]

[TD="class: xl65, align: center"]10/31/2018
[/TD]
[TD="align: center"]Tumbling
[/TD]

[TD="class: xl65, align: center"]10/31/2018[/TD]
[TD="align: center"]Cleaning
[/TD]

[TD="class: xl65, align: center"]10/31/2018
[/TD]
[TD="align: center"]Passivation
[/TD]

[TD="class: xl65, align: center"] 10/31/2018
[/TD]
[TD="align: center"] Anodizing
[/TD]

</tbody>

I am having a brain fart on finding a solution for this any help would be greatly appreciated.

 

[Joe4]

Yes, you can do this by using a COUNTIFS formula in your Conditional Formatting.

So, let's say that your data is in A1:B10, where row 1 is your header/titles.
Then, select the range A2:B10, and enter this Conditional Formatting formula (EXACTLY as I have written it):

=countifs($A$2:$A2,$A2,$B$2:$B2,$B2)>1

and choose your formatting option.

This will do what you have shown above.

 

[Ben85]

Yes, you can do this by using a COUNTIFS formula in your Conditional Formatting.

So, let's say that your data is in A1:B10, where row 1 is your header/titles.
Then, select the range A2:B10, and enter this Conditional Formatting formula (EXACTLY as I have written it):

=countifs($A$2:$A2,$A2,$B$2:$B2,$B2)>1

and choose your formatting option.

This will do what you have shown above.

Thank you Joe4, That worked just as I needed. I speculated it was doable with a countifs formula, but I wasn't 100% how to correctly code it.

Thanks a ton I really appreciate it!

-Ben

 

[Joe4]

You are welcome!

The key is to lock down the starting cell ($A$2, $B$2), but set the ending row equal to the row the formula is in, and allow it to "float" as you move down the column ($A2, $B2).
That creates a running count, so the first instance is not conditionally formatted, but each duplicate it.