Solved

Excel  check for double entries

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
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.

809 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