Pages

Advertisement

Monday, December 17, 2007

Excel Tips : The easiest way to find Duplicates of cell in a column

--------------------------------------------
Dates in column A   Text in Column B
--------------------------------------------
03/10/2003 | AAA
03/15/2003 | BBB
03/20/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC
03/25/2003 | AAA
03/30/2003 | BBB
04/04/2003 | CCC



1> Enter the formula : =A1&B1 to cell C1 and copy / paste the formula to cells C2:C12

2> Enter the formula : =IF(COUNTIF($C$1:C1,C1)>1,"Duplicate","Unique")
                                      to cell E1 and copy / paste the formula to cells E2:E12

 

 

** (you can use the second formula directly if you have only a single column  : Like

Column A

--------------

<>
</>
Column A        
BBB        
CCC        
AAA        
BBB        
CCC        
AAA        
BBB        
CCC        
AAA        
BBB        
CCC        

Insert this Formula in Column B : =IF(COUNTIF($A$1:A1,A1>1),"Duplicate","Unique")

and  you are done ..

Technorati Tags: ,,,

2 comments:

Anonymous said...

This doesn't work!

S3 Technology Softwares said...

it do works buddy .... m using this formula for 2 years ..... to find duplicates ...

there may be prob if u are putting this formula in csv file ... so for that first the column in which u want to find the duplicates convert that column from text to column ... and then apply the formula ...