Check for duplicate entries

April 30, 2010 at 08:53:40
Specs: Windows XP
Hi All,

I recently joined this board and already I received lots and lots of help. I hope it will be okay to ask another question.

Is there a way that we can check for duplicate entries in a spreadsheet. Actually just duplicate entries in one particular column.

For example I paste info from columns A to E from sheet 1 to row 10 in sheet 2. Is there a way we can be prompted if the entry in row 10 column E matches an earlier entry under column E??? It doesn't necessarily have to be a message box or anything. Even highlighting the cell is enough.

See More: Check for duplicate entries

Report •

April 30, 2010 at 08:59:06
Here is one way using Conditional Formatting:

1st - Highlight Column Data Cells
I.E. Cells E1 thru E100

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:


Sub Menu Format :
6th - Patterns
7th - Choose a pretty color
8th - Press OK
9th - Press OK

All your duplicate records should now be highlighted in your selected pretty color


Report •

April 30, 2010 at 10:01:35
it doesn't work quite as what we were expecting. it higlight the cells above the duplicates. For example if row 3 contains A and row 8 contains A. It highlights the contents of rows 2 and 7.

Report •

April 30, 2010 at 11:00:42
Following Mike's exact instructions works as expected in my worksheets. E3 and E8 are highlighted if they both contain A.

However, to answer your specific question I'd guess we need to know what you mean by "an earlier entry under Column E".

Do you mean earlier as in an entry in E1:E9 or do you mean earlier as in entered anywhere in Column E prior to the pasting of the value into E10?

In addition, you said:

Even highlighting the cell is enough.

Which is the "the" cell that you want highlighted? E10 or the duplicate or both/all duplicates?

Report •

Related Solutions

April 30, 2010 at 12:20:39
it works properly already... i just removed the rows in the formula... it now looks like this..


it shows E10 because the first entry is in E10. It now highlights all duplicates...


Report •

Ask Question