Check list for duplicates and display message

**vote4pedro** · 08-30-2008, 12:22 AM

Hello,

This may be a simple question, but I only know the basics of Excel.

I have been given the assignment of creating a spreadsheet that needs to "flag" or give me some sort of prompt when the same name is being added.
For example I have 300 user names on a spreadsheet and if I were to type a name that is already on the list< I would like for Excel to prompt me or flag that name and notify that the name is already in the list. Is this possible?
If so could someone please step me thru this process.

Thanks Alot

**martindwilson** · 08-30-2008, 02:46 AM

use conditional formatting a good explanation is given here

http://www.cpearson.com/excel/Duplicates.aspx
scroll down to
"Highlighting Duplicate Entries"

**Leith Ross** · 08-30-2008, 04:55 AM

Hello Vote4pedro,

Welcome to the Forum!

Your title is important. It will help others to answer your question quickly and enable others seeking answers to the same or similar problems.

Please change your title to accurately reflect your question. In this case, something like "Check list for duplicates and display message". The forum rules will explain how to change your title.

Sincerely,
Leith Ross

**vote4pedro** · 08-30-2008, 09:36 AM

Hello,

Thanks martindwilson for your reply and information, I do greatly appreciate it.

Leith Ross, sorry for the wrong title of thread, it has been more than 2 hours and the directions say to get an moderator to correct this. Thanks again

* To change a Title on your post, Click EDIT then GO Advanced and Change your title, if 2 hours have passed ask a moderator to do it for you...

**arthurbr** · 08-30-2008, 10:02 AM

Hi,
which thread title do you want to use?

**vote4pedro** · 08-30-2008, 12:49 PM

"Check list for duplicates and display message" will be fine

thanks alot

**vote4pedro** · 08-30-2008, 06:09 PM

I know that I'm doing something wrong, but I cant get the code to work.

I only know the basics, how would I insert this code if I wanted it to highlight usernames when one is typed as a duplicate?

Example: Lastname, First
and if I was to put the use in again on say cell J100 then I would receive a notification of this duplicate.

Sorry for bugging you guys, this is new territory for me.

Thanks

**martindwilson** · 08-30-2008, 07:19 PM

select cells in column J down to where your list is likely to end
say J1 to J300
from toolbar select
format
then select
conditional formating
a new menu window will appear
from the drop down menu on left change from
cell value is
to
formula is
enter this formula in box to the right
=COUNTIF($J$1:$J$300,J1)>1
click the format button
click the patterns tab
choose a colour
click ok
click ok again
now any duplicate value typed in j1:j300 will change to that colour
please note duplicate means just that
wilson,martin is not a duplicate of wilson , martin
as spaces are looked at as well.

**vote4pedro** · 08-30-2008, 08:30 PM

Thanks, martindwilson

I got that to work, perfect

One more question....

Say I have 3 sheets within a spreadsheet and i would like the data I enter in sheet 1 to flag duplicates that may be entered already into sheet 2

Thanks again