Prevent duplicates in a range

[reaper_2]

I know it's pretty simple to prevent duplicates in a column, using data validation or VBA - but how do you do it across multiple cells?

say I've got a spreadsheet where A2 is Surname, B2 is Forename, and C2 is a date value, I want to prevent users duplicating all three.

So each column can contain duplicates, but there cannot be the same combination of each three appearing more than once. The simplest thing I though would be to concatenate (A2,B2,C2) and stick custom validation on the resulting formula, but that didn't seem to work. I've also tried a few VBA bits I picked up from various online places but they all relate to single columns it seems.

any ideas?

 

[ayazgreat]

Hi
If you are using Excel 2007 or 2010 then you can use Countifs function through Data Validation for the same like, but put Data Validation in last col.

=COUNTIFS($E$1:$E$7,E2,$F$1:$F$7,F2,$G$1:$G$7,G2)=1

 

[reaper_2]

thanks, we're using 2003 I'm afraid.

Also - there is already validation on column C to ensure the date value is entered correctly. Therefore can't add custom validation to this one.

Does anybody know hoe to achieve this with VBA?

ta