How to get data validation to work when a duplicate value is pasted into a column

[Research RN]

I have to enter patient's SSN's into my database. The fastest and most error free way to do this is simply copy the SSN from the electronic chart and paste into the appropriate cell in the database. I don't want duplicates. So, I need the database to alert me when I paste in a duplicate SSN.

I know you can set data validation to do prohibit duplicates by using a formula. In the attached spreadsheet the SSN is in column B, so:

=countif(B:B,B1)=1

prevents duplicate SSN's from being entered.

However, it only works when a duplicate value is typed into the cell. It doesn't work when a value is pasted into a cell. Also, I know you can use the "validation" option in "paste special", but that's another step to remember everytime I cut and paste. And sometimes I forget.

Is there a way to get data validation to work when a duplicate value is pasted in to a cell?