+ Reply to Thread
Results 1 to 3 of 3

Removing duplicate rows using only a few columns.

  1. #1
    Registered User
    Join Date
    03-27-2015
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    1

    Question Removing duplicate rows using only a few columns.

    Hello. I have encountered a problem I just cannot solve myself, if someone could help me out I would be eternally grateful. I have been assigned with the task of organizing a database consisting of user details. As follows - Date, First name, Last name, Email, House phone, Work phone, Mobile phone, Address, City, State, Zip, etc. This Database is a product of two databases being merged into one, therefor there are a lot of duplicate accounts. At the same time the information per row is different, so the duplicates are hard to find. Let me explain - If I highlight all and go into "Data" - "Remove Duplicates" I will get 25 results. Basically 25 rows that are completely the same, but if I highlight only the "Email" column and remove duplicates specifically from that column, I get around 6 thousand results, basically 6 thousand duplicate emails removed. (I obviously can't just leave it at that since I need to remove the entire row corresponding with the duplicate email and not just the email itself.) Something I have found is that usually the duplicate account doesn't have a single phone number. So what I am trying to do is; have excel remove the row of a duplicate email that doesn't have a corresponding phone number. One way I have thought of doing that is going into "Home"-"Conditional Formatting"-"Highlight Cells Rules"-"Duplicate Values" and then program a macro to check if the text in column "D" (Email) is highlighted, if yes then check the next 3 cells to the right (House phone, Work phone, Mobile phone), if they are empty then remove the row entirely. Though I have no idea how to implement this, neither can I do this manually because I have a total of about 20 thousand rows.

    If you have any sort of ideas I would love to hear them.

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Removing duplicate rows using only a few columns.

    I believe you can highlight your entire data, and use only the email as the key column to look for duplicates. Excel will remove the entire row.

    Edit: First sort your data ascending, so the rows with phone number is above (at the top of all data)
    多么想要告诉你 我好喜欢你

  3. #3
    Registered User
    Join Date
    02-25-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    46

    Re: Removing duplicate rows using only a few columns.

    Try running the following macro:

    Please Login or Register  to view this content.
    You will need to change the "D" in "D1" to whatever your last column is and add numbers to the Array() function according to the number of columns you have.

    (Edit: To clarify, this will remove completely identical rows, so I'm not sure if it's exactly what you need if some entries are blank.)
    Last edited by Ezzard; 03-27-2015 at 09:51 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing duplicate columns
    By Rec1ne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-02-2013, 06:04 PM
  2. Removing Duplicate Rows based on 2 columns
    By metalpoker in forum Excel Programming / VBA / Macros
    Replies: 37
    Last Post: 10-14-2011, 05:28 AM
  3. Removing Unwanted Rows and summing the duplicate Rows
    By Cena in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2010, 07:00 AM
  4. Replies: 1
    Last Post: 04-01-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1