Solved: Data Validation: Unique Names

[johnske]

I have two columns with the headers 'First Name' and 'Surname'. I want to apply (standard) data validation so that the concatenation of First Name & " " & Surname is unique. i.e. If the name has already been entered it cannot be entered again. What formula would I need to use?

TIA,
John :)

[lucas]

John, this works for 1 column....
=COUNTIF($A$2:$A$20,A2)=1

[johnske]

Thanx Steve,

I got that to work OK, but the syntax for the formula concatenation's got me beat :)

Regards,
John

[lucas]

If you can do the concatenation first couldn't you extract a unique list from that?

[matthewspatrick]

John,

Following Steve's suggestion, I made a third column that concatenated the first two, and then used this custom validation on the surname column:

=COUNTIF(C:C,A2&" "&B2)=1

It appeared to work...

[johnske]

Hi Patrick,

Yes, I got that to work, but it doesn't seem very elegant and it also raises the problem - what about users (such as myself) that take the shortcut of selecting entire rows to clear the contents of a few cells? - (the formula you're using for the validation is then cleared)

Regards,
John

[matthewspatrick]

John,

Data Validation is fallible on many counts. You described something that would wipe it out here; it also will not prevent a copy/paste.

It's sounding like a Worksheet_Change sub is what you're looking for...

[johnske]

OK, thanx guys,

I can work around this by hiding the row and column headers and setting a scroll area...

Regards,
John :)

[Bob Phillips]

Why not just a csutom type with a formula of

=SUMPRODUCT(--($C$9:$C$14=C14),--($D$9:$D$14=D14))>1

in DV?

[johnske]

I can't get that to work Bob...

The 1st FirstName and Surname is in B4 and C4, so what should the DV formula be in (say) B8 and C8? :)