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?

[martindwilson]

as far as i know data validation is rendered ineffective on paste. perhaps the way to go is to use vba to prevent paste into cell.

[JBeaucaire]

The simplest of all answers is to do your pasting within the cell rather than ONTO the cell.

In the cell that has the SSN you're going to copy, press F2, then Shift-HOME to highlight the SSN, then CTRL-C to copy the SSN directly. You're not copying the whole cell including it's formats and such, you're copying the data IN the cell into you're computer's memory.

Now select your target cell, press F2 again, then CTRL-V directly into the cell rather than onto it.

This will not break any validation or conditional formatting, and is something you could get used to doing.

[Research RN]

The simplest of all answers is to do your pasting within the cell rather than ONTO the cell.

In the cell that has the SSN you're going to copy, press F2, then Shift-HOME to highlight the SSN, then CTRL-C to copy the SSN directly. You're not copying the whole cell including it's formats and such, you're copying the data IN the cell into you're computer's memory.

Now select your target cell, press F2 again, then CTRL-V directly into the cell rather than onto it.

This will not break any validation or conditional formatting, and is something you could get used to doing.

Thanks, I'll try that.