Identifying unique dates in a range of cells containing dates...

[Guest]

I have dates entered in cells A1 through H1, some of which are duplicate
dates and others which are unique dates.

What I need is a formula I can write in cell I1 which will tell me if the
date in cell A1 is unique in comparison to all the other dates in the range
A1 through H1.

I also need to extend this formula across cells J1 through P1 to repeat the
analysis for each of the respective dates in the data range. (i.e. I need
the formula in cell J1 to tell me if the date in cell B1 is unique in
comparison to all the other dates in the range A1 through H1).

Thanks for the assistance in advance!

 

[Dave O]

Here's how to do the first part:
=IF(COUNTIF(B1:H1,A1)=0,"Unique", "Not unique")

There's a logic problem in the second part of your question: "to tell
me if the date in cell B1 is unique in comparison to all the other
dates in the range A1 through H1". B1 is included in that range. Or
do you need to check if B1 is repeated in A1 and C1:H1?

 

[Dave Peterson]

Take a look at =countif() in excel's help.

=countif(a1:h1,a1)
would return the number of cells that match the value in A1

=if(countif(a1:h1,a1)=1,"Unique","Duplicates somewhere")

 

[Earl Kiosterud]

C,

This formula in I1 will yield TRUE or FALSE if depending on whethere there's
a dup. Copy it to the right with the fill handle to P1. The cell with the
dup will also yield TRUE. These cells can be copied down into succeeding
rows, but won't work in other columns without modification.
=COUNTIF($A$1:$H$1,A1)>1

You can make it say something like "DUP" with something like this. Same
deal with any dups.
=IF(COUNTIF($A1:$H1,A1)>1,"DUP","")

You might find it more useful to identify the dups in situ with conditional
formatting. That will make the dups change color, go bold, etc. You won't
need anything in I1 to P1 for this solution. To do that,

Select A1:H1. A1 should be the active (white) cell. Press Enter until it is
if necessary, while the cells are selected.
Format - Conditional formatting.
In the dropdown, select "Formula is."
Paste this formula in (from here): =COUNTIF($A1:$H1,A1)>1
Click "Format" and choose some formatting for how you want the dups to look.

It's possible to have only a dup, but not its original, be identified, but
it's a little trickier. Post back if you're interested.

 

[Carim]

Do you mean that :

=IF(COUNTIF($A$1:$H$1,A1)=1,"Unique","Not Unique")


does not work ...???


Carim