Avoiding duplicate entries

[dasadler]

I am trying to avoid scheduling the same person for two jobs on the same day.

If I have two columns... one with a date and the other with a name (see image), It is possible that I might enter a name/date combination that has been previously entered and I would like to avoid this. Other than being careful and diligent, is there a way to avoid this? I am thinking maybe conditional formatting if that name/date combination already exists? If CF is the answer, how can I construct a formula to check all the name/date combinations previously entered? In the example below, cells A6:B6 should warn me that it is a duplicate entry.

[HansV]

One option is to use conditional formatting:
- Select the range you want to format conditionally. Let's say that A2 is the active cell within the selection.
- Activate the Home tab of the ribbon.
- Click Conditional Formatting > New Rule...
- Click 'Use a formula to determine which cells to format'.
- Enter the following formula, adjusting the ranges as needed:

=SUMPRODUCT(($A$2:$A$6=$A2)*($B$2:$B$6=$B2))>1

- Click Format... and specify the desired formatting, e.g. red as fill color.

Another option is to use data validation.
- Select the range you want to apply validation to. Let's say that A2 is the active cell within the selection.
- Activate the Data tab of the ribbon.
- Click Data Validation.
- Select Custom from the Allow dropdown.
- Enter the following formula into the Formula box, again adjusting the ranges as needed:

=SUMPRODUCT(($A$2:$A$6=$A2)*($B$2:$B$6=$B2))<2

- Activate the Error Alert tab and enter a suitable error message.
- Click OK.

You can use both conditional formatting and data validation if you like.

[dasadler]

Thank you. I find that I often add rows to this schedule - sometimes in the middle and sometimes at the end and periodically sort by date. Which method would you recommend that would be most transparent (meaning I would not have to format new rows as I enter them)? I am inclined to think data validation.

[HansV]

One option is to apply the conditional formatting and/or data validation to a larger range than you expect to need, say 1,000 rows. You'll have to adjust the ranges in the formulas, obviously.
But don't make the ranges too large - processing the formulas would have a negative impact on performance.

Another option is to extend the conditional formatting and/or data validation to a few rows below the range you use now. When you want to add new data, don't simply type in the first unused row, but insert a new row. This will automatically adjust the conditional formatting and validation range.