How to avoid entering dublicates in Excel 2010 sheet?

[PMJ99]

I have a simple list of serial numbers, growing from top to down every time I enter a new serial number. But now, when I'm extending the list with a new number, I have to hit ctrl+f to find if I have already entered that number. Is there any automatic way to check if that number I'm about to enter is already on that sheet somewhere?

Thanks.

[boooo]

heya, I am new here myself, so i hope its ok to post a reply......

Personally i think i would use Data Validation

Highlight the column you want to 'validate' and then choose Data>Validation from the tool bar

in the settings tab, choose custom and then type into the formula box

=countif(A:A,A1)=1

with "A" being whatever column your data is actually in

on the Error Alert tab, you have the choice of not allowing duplicates at all, or giving the user the option to override and accept the duplicate...

HTH, (and I hope I have understood correctly what you were asking too)

[PMJ99]

Hi boooo,

I still have few problems. I couldnt find any options for duplicates in Data validation settings. And also that formula couldn't be accepted by Excel.

Its just a one column with numbers, which is growing all the time. The problem is that I don't want to hit ctrl+f every time I add a number to check if the number already is in the list.

[mduffel]

Have you tried using conditional formatting to highlight a duplicate entry after you enter the new serial number?

[mduffel]

For conditional formatting...

Highlight the column that you are entering data in then >Home>Conditional Formatting>Highlight Cells Rules>Duplicate Values

When the duplicate values window pops up just hit enter and it will highlight any duplicate values light red if you are using Excel 2010 or you can choose what type of formatting you wish. Then you can just delete the entry if it is a duplicate.

I've never used the data validation method described above however it worked fine for me.

Sorry I got distracted and posted the conditional formatting question before I entered the instructions.

Hope this helps.

[PMJ99]

That conditional formatting helps a lot!

Thank You!

[mduffel]

Your welcome.