Let Excel 2007's new Remove Duplicates feature find unique values in your data

Report as spam Discussion - Post 3 of 4

What I do to identify duplicates

So, when I want to find duplicates in my excel files, I use the following:
This requires that the data be sorted by the field that you want to check for duplicates as well as having a header row. Say the column is titled "Email". Sort your data by the "Email" column.
Insert a new column directly next to the "Email" column. Name it "Duplicate".

Note: This assumes that the "Email" column is the A column and the "Duplicate" column is the B column.

In B2, use the following formula:

=if(A2=A1, "Duplicate", "Unique")

Fill down to the end of the list - so you should see =if(A3=A2, "Duplicate", "Unique")
etc.

Select the "Duplicate" column. Copy then paste special->Values. Select the column again, go to Format->Conditional Formatting. Set it to "Cell Value is equal to Duplicate. Then select the format to set the pattern to a bright color.

I do formatting part just for a visual reference.

But, once you have the data in the cells, you can sort by the duplicate column and find all of the duplicate records easily.

Posted: 03/04/2008 @ 02:51 PM (PST)

beegkitty@...: Job Role: Development Project Management; Location: Tracy, California; Member since: 03/04/2008

View Profile | Send Message

Post a Reply Save

« Previous | Next »

Print/View all Posts | Subscribe to this Thread

Read original item: Let Excel 2007's new Remove Duplicates feature find unique values in your data

Excel - Remove Duplicates - retain the duplicates in a subset?lloydb@... | 02/27/2008 @ 06:29 AM (PST)

I'm sure you're rightssharkins@... | 03/01/2008 @ 07:55 AM (PST)

What I do to identify duplicatesbeegkitty@... | 03/04/2008 @ 02:51 PM (PST)

Identify Duplicates Using Conditional Formattingalpuod@... | 09/25/2008 @ 08:16 AM (PDT)

SponsoredWhite Papers, Webcasts, and Downloads

Achieving Cost and Resource Savings with UC white paper Microsoft Read how UC can help your company save by reducing out-of-pocket expenses, consolidating communications infrastructure, and leveraging human capital. Download Now
Easily Monitor Virtual/Physical/Cloud and Save Budget. up.time - Free Trial Uptime Software Easily manage, measure, and monitor all your physical,�virtual, and cloud assets across platforms, applications, domains, and multiple datacenters.� Download Now
Top 7 Things You Should Know About Activation and Genuine Windows Microsoft Learn the top 7 things you need to know about volume activation, including product keys, experience, deployment, virtualization, resources and more. Download Now

Essential Topics

White Papers, Webcasts, and Downloads

Unrivaled support from Novell, now available for Red Hat Novell If Linux is going to power your mission-critical applications, you'd ... Download Now
The Compelling Case for Conferencing Microsoft Discover how today's conferencing solutions can help your organization improve business outcomes and reduce costs in challenging economic times. Download Now
Driving business agility through SOA connectivity and integration IBM Corp. This paper describes some of the business and IT issues that enterprises ... Download Now

Top Rated News on ZDNet

Microsoft exposes Firefox users to drive-by malware downloads +86 rating
What Microsoft won't tell you about Windows 7 licensing +75 rating
Seven perfectly legal ways to get Windows 7 cheap (or even free) +74 rating
Is it OK to use OEM Windows on your own PC? Don't ask Microsoft +74 rating
Windows 7: An impressive upgrade +73 rating

Browse by Tag

networking: Popular tags: windows, software, hardware, security, it management
e-mail: Popular tags: windows, software, networking, security, hardware
hardware: Popular tags: windows, software, networking, it management, security
programming: Popular tags: software, windows, networking, hardware, it management
software: Popular tags: windows, hardware, networking, programming, it management
windows: Popular tags: software, networking, hardware, security, it management
linux: Popular tags: software, windows, networking, hardware, security
it management: Popular tags: networking, windows, software, security, hardware
career: Popular tags: it management, networking, software, windows, feedback
security: Popular tags: networking, windows, software, it management, hardware
off-topic: Popular tags: software, windows, hardware, feedback, networking
feedback: Popular tags: software, windows, hardware, networking, it management
project management: Popular tags: it management, software, networking, windows, programming

500 Things Every Technology Professional Needs to Know: Did you know Microsoft's RegClean does not work with XP but you can use shareware to clean your registry? Did you know most wireless access points don't have encryption enabled by default? Did you know there are 500 tidbits of information contained in TechRepublic's 500 Things Every Technology Professional Needs to Know that will help you become a successful IT professional.; Buy Now

IT Professional's Guide to Policies and Procedures, Third Ed: Whether you're creating policies for management, training, personnel, support, privacy, Internet/e-mail usage, security, or inventory, you'll meet the needs of your entire enterprise with this one download!; Buy Now