How to find duplicates in column without deleting them

It is a common requirement to find duplicates, in most cases the duplicates need to be deleted, but in some cases the duplicates need to be kept.

Excel 2010 (even 2007?) got the feature under "DATA" to remove duplicate entries in column(s), called Remove Duplicates. 

 

The issue with this tool it removes the duplicates and do not give more details.

If you want to mark the duplicates or want to VLOOKUP unique values you need use COUNTIF.

Let's see the example below, 

There are many duplicate entries i  column D. If you want to put "Duplicate" in column C, use

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

The result will be,

Important to note, if there is two or more values, all considered "DUPLICATE". This will not help if you are looking to use unique values. For example if you try to index the unique values, let's say in column B.

You may want to use,

=IF(C2="DUPLICATE","",MAX(B$1:B1)+1)

The result will be a "1" in B15. In other words there is only one unique value in column D. But we know it is not true. To solve this we need to change the COUNTIF slightly.

Instead of 

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

We will use, 

=IF(COUNTIF(D$2:D2,D2)>1,"DUPLICATE","")

and the result is,

Now you can use VLOOKUP to pull the unique values, Vlookup(1,B:D,2,FALSE) and so on.

The Key Point is to leave the first instance of the duplicates as "Unique".



There will be lot of other ways to do the same. 
I will be happy to have any feedback.

How to use CONCATENATE to create TAG or Labels

Prerequisite 

Know how to use "CONCATENATE", "MAX", and "IF" individually. Please refer to Microsoft help for 

Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.

Possible Usage

1. blogging - For those doing blogging regularly for tagging or labeling

2. Tagging for photo (like when you upload to Photobucket etc...)

The steps (How to do)

1. Key in the tags or labels in a column. Please note that the first raw is not used.

2. In cell A2 key in the formula, =IF(B2="A",MAX(A$1:A1)+1,"")

3. Drag or copy paste the formula all the way (to cover the all the tags or labels)

4. Key in "A" in column B in the corresponding raw you need.

5. In column E, key in =max(A:A)

6. In cell F2 type "1"

7. In cell F3 type =IF($E$1<=F2,"",F2+1)

8. Drag or copy paste the formula all the way (to cover the all the tags or labels)

9. In Cell G2 key in formula =IF(F2="","",VLOOKUP(F2,A:C,3,FALSE))

10. Drag or copy paste the formula all the way (to cover the all the tags or labels)

11. In cell H2 key in =CONCATENATE(G2,",",G3,",",G4,",",G5,",",G6,",",G7,",",G8,",",G9,",",G10,",",G11)

12. copy the content to the blogger or any other site

DONE