1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: deleting all duplicates in excel

Discussion in 'Business Applications' started by janrene, Dec 6, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. janrene

    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?
     
  2. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    77,766
    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.
     
  3. scotty718

    scotty718

    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.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    @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
     
  5. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    77,766
    hola, Zack......how's the Great NW? :)
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Starting to get c o l d ! Otherwise good! :) Busy as always though lol
     
  7. janrene

    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
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  9. janrene

    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!
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  11. janrene

    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!
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Solved deleting duplicates
  1. ImaGrump
    Replies:
    2
    Views:
    673
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1029952

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice