Solved

Excel  check for double entries

Posted on 2002-03-03
6
3,486 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now