?
Solved

Excel  check for double entries

Posted on 2002-03-03
6
Medium Priority
?
3,502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 400 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 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