Check for dual key Duplicates

  • Thread starter Thread starter bryan.t.jackson
  • Start date Start date
B

bryan.t.jackson

I have a workbook where I need the user to be able to enter only one entry
for a two column pair.

Column A is an identifier number and Column B is a date, so:

#1, 10/16/2009
#1, 11/16/2009
#2, 10/16/2009

is valid but,
#1, 10/16/2009
#1, 10/16/2009
#2, 11/16/2009

would not be valid.

I've thought about concatenating these two fields in another sheet and then
doing a vlookup on this table and if it is N/A it would mean it's not
previously used, but that seems clunky...As well as doing a loop to determine
if it's been used since this sheet can have thousands of entries.

Is there a simple way of doing this that I might be missing?
 
Hello Bryan,

You could try the following.
Assume that the numbers are in column A and the dates are in column B.
In column C concatenate columns A and B with the following formula. (The
example is in cell C2 assuming row 1 has column headers.)

=A2&TEXT(B2,"mmddyyyy")

Copy the formula down in column C for the likely number of rows required.

Select the cells in columns A and B starting at row 2 (cells A2 and B2) for
the number of rows that are likely to be used.

Select Data Validation. (xl2007 in Data ribbon in Data Tools block. Earlier
versions of xl Data->Validation. )

Select Custom under the Allow: field and insert the following formula.
(Assumes that the first cell in the selection is A2.)

=COUNTIF($C:$C,$C2)<=1

At this point there is no need to edit the Input message and Error alert
tabs but you can do that later to enhance if required.

Now each time values are entered in columns A and B, only one unique
concatenated combination is permitted.
 
Back
Top