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

 

[Rossjp]

I'm just wondering if a macro (VBA) solution would work better? Ultimately I'd like to identify all duplicate song entries and by a seperate macro be able to delete all but the first occurrence of a duplicate song title entry.

Sorry I'm so limited in my understanding of advanced Excel operations. I have worked some with macros (VBA).

Thanks,

John

 

[VoG]

I wonder if some values have trailing spaces. For example here C6 contains 'Crazy ' not 'Crazy' and is not marked as a Dup.

<b>Sheet5</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;" /><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><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</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 >Crazy</td><td >Dup</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><td > </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><td > </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><td > </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 >Crazy</td><td >Dup</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >Crazy </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D1</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C1)</span>>1,"Dup","")</td></tr><tr><td >D2</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C2)</span>>1,"Dup","")</td></tr><tr><td >D3</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C3)</span>>1,"Dup","")</td></tr><tr><td >D4</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C4)</span>>1,"Dup","")</td></tr><tr><td >D5</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C5)</span>>1,"Dup","")</td></tr><tr><td >D6</td><td >=IF(COUNTIF<span style=' color:008000; '>(C:C,C6)</span>>1,"Dup","")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


You could try selecting all the values in column C then running the TrimAll macro http://www.mvps.org/dmcritchie/excel/join.htm#trimall