How to detect uniques and count the number of cells with duplicate data

Question

I have a column of data that has the some of the same values in it. What I want to detect is if that row is unique. If it is, echo 'unique' (or something) but if it's not, count the number of duplicates in the column and echo that number. Example of what I want:

COL A     RESULT COLUMN
Apple     Unique
Banana    Unique
Banana    3
Banana    3
Cherry    Unique
Date      Unique
Date      2

Example of what I have using this code (=IF(A1=A2, "Duplicate", "Unique")):

COL A     RESULT COLUMN
Apple     Unique
Banana    Unique
Banana    DUPLICATE
Banana    DUPLICATE
Cherry    Unique
Date      Unique
Date      DUPLICATE

I guess what I really need is a query inserted(?) into where the 'Duplicate' text is in that formula that will count down the same cell values until it sees a different value, and echo that number.

If it can be done within the same column excellent. If I need another column and have to perform two sets of queries I can compromise.

UPDATE BASED ON DATATOO ANSWER

I ran that query, modified column 'A' to 'E' and this is what returned.

City Country Merge  Duplicate City Country
Aberdeen, UK    Unique
Aberdeen, UK    2
Bangor, UK          2
Bath, UK            Unique
Belfast, UK         Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique
Birmingham, UK  Unique

As you can see this doesn't count right. Thoughts?

Answer 1

I know this is an old question, but I see that it was never satisfactorily answered, so I hope this is still useful.

You want this:

=if(COUNTIF($A$1:$A1,A1)=1, "UNIQUE", COUNTIF($A$1:$A$7,A1))

This will fill down and look like this in subsequent rows:

=if(COUNTIF($A$1:$A2,A2)=1, "UNIQUE", COUNTIF($A$1:$A$7,A2))
=if(COUNTIF($A$1:$A3,A3)=1, "UNIQUE", COUNTIF($A$1:$A$7,A3))
...

And these are the results (assuming the formula was inserted into B1 and filled down):

    A         B
1 Apple     UNIQUE
2 Banana    UNIQUE
3 Banana    3
4 Banana    3
5 Cherry    UNIQUE
6 Date      UNIQUE
7 Date      2

The key to your problem is that you're looking for the first occurrence of the string to count as unique, then you count the duplicates. So, for the first part of the formula, you're really only searching the list so far, not the entire list.

This method also has the added advantage of working with an unsorted list as well as a sorted one.

Answer 2

I know your example shows banana as unique, and you may be asking for the first instance of a row to display that way, but if not.....

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

apologies if you truly intend the first row of an item to show as unique