Solved

Excel  check for double entries

Posted on 2002-03-03
6
3,494 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:cybernursery
6 Comments
 
LVL 8

Expert Comment

by:starl
ID: 6838152
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
 
LVL 9

Accepted Solution

by:
antrat earned 100 total points
ID: 6838190
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
 

Expert Comment

by:ComTech
ID: 6840370
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question