Finding duplicate cell entries

Rossjp

Board Regular
Joined
Nov 14, 2005
Messages
67
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

odarestephen

New Member
Joined
Nov 10, 2008
Messages
25
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

Well-known Member
Joined
Dec 4, 2009
Messages
591
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

Board Regular
Joined
Nov 14, 2005
Messages
67

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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-style:eek:utset;border-color:#f0f0f0; ">C</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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-style:eek:utset;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

Board Regular
Joined
Nov 14, 2005
Messages
67

ADVERTISEMENT

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Nov 14, 2005
Messages
67
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top