Excel check for double entries

Dear Experts,

When entering data in excel, is there some kind of formula to help you identify whether you have repeated data in the same column.  Say I enter 300 ID Card numbers and I would like to check whether they are all unique or whether I have some repetitions.  I just want to know how many cases of repetitions there are.  I don't want a primary key field like in Access that does not allow you to enter a repeated value.

Thanks
cybernurseryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
antratConnect With a Mentor Commented:
Hi Cybernursery

I have a few ways to deal with duplicates on my site here:
http://www.ozgrid.com/Excel/Formulas.htm

In regards to the "Preventing duplicates" this can be set to only inform of a duplicate being entered, rather than prevent.

If as you say you only want to get a count of duplicates use this simple formula
=COUNTIF($A$1:$A$400,A1)
And copy it down as far as needed. It assumes your entries are in the range $A$1:$A$400. It's important to note the absoluting of the range $A$1:$A$400

Kind Regards
Dave Hawley
www.MicrosoftExcelTraining.com
www.OzGrid.com
If it's Excel, then it's us!

0
 
starlCommented:
can't think of any formulas but there are some built-in items:
1. you can always due a unique filter (Data - Advanced Filter - check unique items)
2. when entering info in the same column, excel will do an autofillin for that column.

other than that, all I can think of is vba code.
0
 
ComTechCommented:
Comment will be allowed, was advised of improper advertising in Community Support.  I have re-thought my position on this, and have placed the comment back to it's origal state.

My mistake,
ComTech
CS Admin @ EE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.