How to determine uniqueness and count the number of cells with duplicate data in Google spreadsheets

Question

Having a little problem with Google Spreadsheets.

I have a data column that has the same values. What I want to discover is a unique string. If so, echo is 'unique' (or something), but if it does not count the number of duplicates in the column and displays that number.

An example of what I want:

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

An example of what I use for 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 assume that I really need a query (?) In which the text "Duplicate" is in this formula, which will count the same cell values ​​until it sees a different value, and repeat this number.

If it can be done in one column perfectly. If I need another column and I need to execute 2 sets of queries, I can compromise.

Many thanks to those who can help.

Dave

DATATOO RESPONSE UPDATE

I fulfilled this query, changed column "A" to "E", and this is what was 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 is not considered correct. Thoughts?

Answer 1

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

Do you want to:

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

This will fill up and look like this:

 =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 out):

  AB 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 are looking for the first occurrence of the string to count as unique, then you count duplicates. So, for the first part of the formula, you really only look at the list so far, and not the entire list.

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

Answer 2

I know that your example shows that a banana is unique, and you can ask for the first instance of the string to display this way, but if not .....

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

apologies if you really intend for the first line of the item to appear as unique