Finding duplicate cell entries

[Rossjp]

I have a lengthy excel spreadsheet in which I catalog all of my Karaoke song collection. I would like to be able to automatically locate (not delete) cells in which there are duplicate song titles. For my purposes just finding these items quickly so I may deal with them in a manner I've already developed is all I want to do. VBA, Macro or whatever would be appreciated. I'm a relative novice with macros and VBA.

For example row 12 may have the song title "Crazy" in column C and later row 1200 may have the same song title. Manually searching the spreadsheet for dupliates is way too time consuming so I think it can be seen why I'm looking to unlock the power of excel to do this for me.

Thanks,

John

 

[VoG]

This should help http://office.microsoft.com/en-us/excel/ha011366161033.aspx

 

[odarestephen]

Hi

One method is to use conditional formatting which will highlight duplicate cells

Foe example highlight column a and select format and conditional formatting

Select formula is and type: =IF(COUNTIF($A$1:$A$14,A1)>1,1,0)

select patterns and select a colour. This will highlight the duplicates in colour.

Another method is inserting a coloumn and insert the formula as above to find duplicates in column A. Where the count is greater than 1 it will give a 1 and if it is an unique entry the formula will return a 0. Do a filter and select the 1s to find all duplicates.

hope that helps.

Steveo

 

[dado6612]

In 2007 is "easier"

Select cells, go to Conditional Formatting->New Rule->Format only unique or duplicate values and then choose style of formatting

 

[Rossjp]

I must be confused. I tried the fomula, copied and pasted to the entire column and no duplicates were identified althought there are duplicates. In my case the info is in column C and consistes of text formatted as text or in general format. What am I missing?

Here's a simple example:

Column C
Crazy
Love Me
Roll On
Poker Face
Crazy

So, I'm wanting to find any duplicates in this column.

 

[VoG]

This works for me

<b>Sheet3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ff0000; ">Crazy</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >Love Me</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >Roll On</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >Poker Face</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ff0000; ">Crazy</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Conditional formatting </b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Cell</td><td >Nr.: / Condition</td><td >Format</td></tr><tr><td >C1</td><td >1. / Formula is =COUNTIF(C:C,C1)>1</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C2</td><td >1. / Formula is =COUNTIF(C:C,C1)>1</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C3</td><td >1. / Formula is =COUNTIF(C:C,C1)>1</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C4</td><td >1. / Formula is =COUNTIF(C:C,C1)>1</td><td style="background-color:#ff0000; ">Abc</td></tr><tr><td >C5</td><td >1. / Formula is =COUNTIF(C:C,C1)>1</td><td style="background-color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Rossjp]

I forgot to indicate that I'm using Excel 2003. I also tried the last post but it did't work for me.

 

[VoG]

It should still work in Excel 2003.

A formula in a spare column as an alternative:

Sheet3

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td style="border: 3px outset rgb(240, 240, 240);">C</td><td style="border: 3px outset rgb(240, 240, 240);">D</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">1</td><td>Crazy</td><td>Dup</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Love Me</td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">3</td><td>Roll On</td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">4</td><td>Poker Face</td><td> </td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">5</td><td>Crazy</td><td>Dup</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D1</td><td>=IF(COUNTIF(C:C,C1)>1,"Dup","")</td></tr><tr><td>D2</td><td>=IF(COUNTIF(C:C,C2)>1,"Dup","")</td></tr><tr><td>D3</td><td>=IF(COUNTIF(C:C,C3)>1,"Dup","")</td></tr><tr><td>D4</td><td>=IF(COUNTIF(C:C,C4)>1,"Dup","")</td></tr><tr><td>D5</td><td>=IF(COUNTIF(C:C,C5)>1,"Dup","")</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

 

[Rossjp]

I'm still unable to get either if the last two solutions to work in my spreadsheet. The first causes ALL CELLS to return as highlighted in red, the second, nothing. Would it help if I emailed a copy of my spreadsheet? If and if you are willing to contunue trying to help out please send me your email address and I'll send it right away.

Thanks,

John

 

[VoG]

Try uploading it to www.box.net and providing a link.