Is there a way to detect duplicate dates as I enter them?

[Karin]

Hi. I need to take an inventory of over 4,000 film can that we have in our vault. First I need to go though get basic information from each can. I created a simple spreadsheet in numbers to track this information. We are tracking these cans by the date on the side of each one. Most of these dates are unique, but there may be some duplicates. What I need is some way for the spreadsheet to alert me to the fact that I am entering a duplicate date when I enter it so I can add an additional indicator to the date. This way I won't have two cans with the same title. Is there a way for Excel to detect duplicate information as I enter it? I had hoped that the autocomplete function might do it, but this only works in adjacent cells.

-Karin

[Colin Legg]

Hi Karin,

You've thrown me a bit of a curve ball there because you're using a Mac version of Excel. I've never used a Mac, so I'll show you how I would do it in Excel 2010 and hope that the same applies for your version.

You want to allow duplicate values but have a way to highlight them. I think a good solution here would be to use conditional formatting.

Select the column and then go to conditional formatting > highlight cell rules > duplicate values > choose the colouring you want and click OK. [See attached screenshot]. At that point any duplicate values should be highlighted.



If the "duplicate value" rule is not available in Excel 2008 then you can use this conditional formatting formula instead.

=COUNTIF($A:$A,$A1)>1

Where $A:$A is the entire column (change to $C:$C or whatever you need) and $A1 is the first cell in that column (change to $C1 or whatever you need).