Checking a duplicate in a concatenated column

Hello,

Ive been trying to work out how to stop some duplicates where a date and a drop down combine.

There are two columns, one with a date, the other is a drop down list, in the third a concatenation of the two.

When the list grows its easy to enter the date and drop down not reading that for the same date the drop down list option was already previously chosen, this results in an output in a summary sheet being incorrect, or a repeated value, and or an incorrect calculation.

I'm hoping to (based on advice) to be able to upon entry of either first date and then list choice or vice versa to check the concatenated row and if a duplicate exists to produce either an error, a message, or to disallow the entry.

I've attached an example spreadsheet as I know this is a bit of a long winded explanation.

Thank you in advance for your taking a look,

Kind regards AI

You don't need to check cell C2, as that is the first in your list, so put this formula in cell C3:

=IF(COUNTIF(C$2:C2,A3&B3),"Already chosen",A3&B3)

then copy down. If you want to make it more robust, you can amend it like this:

=IF(OR(A3="",B3=""),"",IF(COUNTIF(C$2:C2,A3&B3),"Already chosen",A3&B3))

so that it doesn't report on blanks, and then you can pre-copy it down the column as far as you like.

Hope this helps.

Pete

Hi Pete
Thats amazing, no macro needed, thank you so much ++
AI

You're welcome, and thanks for the rep.

Pete