Solved: deleting all duplicates in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

janrene

Thread Starter
Joined
Dec 6, 2011
Messages
11
I am using Microsoft excel 2010. I have an old mailing list of people who have registered for an event in the past. I have a new mailing list of people who have registered for an event this year. I want to merge both lists and delete people who appear on both lists. I do not want the people who have already registered for my event this year to get a reminder to register again this year when they already have, how do I do this?
 

valis

Moderator
Joined
Sep 24, 2004
Messages
78,206
combine them both into one column, say column A. Sort by column A. In column B, enter =a1=a2, autofill down to the end of the data in column A. The entries that say 'true' are duplicates. Copy > special paste column B as values, sort by column B and delete all the rows that have true in column b.
 
Joined
Nov 19, 2010
Messages
185
I am not on Excel 2010, but did it keep the Remove Duplicates function that is built into the Data tab from Excel 2007. This is a very easy wizard to get that job done as well.
 
Joined
Jul 25, 2004
Messages
5,458
@scotty, yes, that functionality is still there.

@janrene, I would recommend scotty's recommendation of utilizing this built-in functionality. You will run into a problem if you have other data associated with it, as in a larger data set. You will get prompted if you want to expand your selection. The way this works is the first instance will be kept, then every other instance of that value will have it's cells deleted and shifted up (not the entire row).

@valis, hi!

HTH
 

janrene

Thread Starter
Joined
Dec 6, 2011
Messages
11
Confused

I am using Microsoft excel 2010. I have an old mailing list of people who have registered for an event in the past. It is in a worksheet with 15 columns and 24000 rows. I have a new mailing list of people who have registered for an event this year. I want to merge both lists and delete people who appear on both lists. I do not want the people who have already registered for my event this year to get a reminder to register again this year when they already have, how do I do this?

Can you give this beginner step by step instructions? I tried what you said but was confused.

janrene
 
Joined
Jul 25, 2004
Messages
5,458
Can you not add a column for the new years registration? Or last registration date, so you could check if it was this year (or whatever time frame you wanted to look for)?

I would discourage having separate lists of virtually the same data. Better to have all of your data in one spot, then slice-n-dice from there.

Is there any chance you could give us a small subset of data as an example? (If so, please keep all personal information off, or put in dummy data, as this is viewed by anyone.) Or at least give us the headers of your data structure. Also, are you utilizing Tables or a PivotTable?
 

janrene

Thread Starter
Joined
Dec 6, 2011
Messages
11
I am using microsoft excel 2010
first document (2011 registrants)
first name last name email address 1 address 2 city state zip

2nd document (2012 registrants)


I do not want to send an invitation to people that are already registered in 2012

Thanks!
 
Joined
Jul 25, 2004
Messages
5,458
You said 15 columns. I count 8. Something is missing. Please explain in greater detail.

How are you sending invitations anyway? Since you have two lists, one for each year, won't you already know who has registered and who hasn't? What exactly is the data which states who has received an invitation for that specific year or not?
 

janrene

Thread Starter
Joined
Dec 6, 2011
Messages
11
I'll start again.

I have a list of names and addresses of people who attended an event in 2011 They are in an excel spreadsheet (2010).

The columns are as follows:
first last email address 1 address 2 city state zip


There is a new event in 2012 and people have begun registering already. These people possibly attended the 2011 event. I pulled them into an excel spreadsheet withe same format listed above.

So I want to make sure that the 2012 people that appear on the 2011 spreadsheet do not get the invitation to register when they have already registered. My first thought was to highlight the 2012 list in red then merge, sort and delete duplicates. Then I would have to manually go in and delete the rest of the highlighted names. Am I making this more complicated than I have to?

Thanks so much for your help!
 
Joined
Jul 25, 2004
Messages
5,458
So can you upload a sample file? Or scrub it of data, leaving the structure in place, and upload it so we can take a look at it? I'm thinking you can get away with having a single input for all of this, but instead of re-creating the wheel, it would be easier and less time consuming to start from where you're at now with it.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top