Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for dual key Duplicates
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for dual key Duplicates
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. -- Regards, OssieMac "bryan.t.jackson" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for Duplicates then Sum cells of duplicates | Excel Programming | |||
check for duplicates | Excel Worksheet Functions | |||
Check for Duplicates | Excel Worksheet Functions | |||
Check for Duplicates (RP) | Excel Programming | |||
Check for duplicates | Excel Worksheet Functions |