+ Reply to Thread
Results 1 to 5 of 5

Duplicates (2 files with different layout)

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    12

    Question Duplicates (2 files with different layout)

    Hello,

    I'm facing a problem and have no idea how to solve this.

    Here is my problem:

    I have 2 excel files. 1 has 10 columns and about 20.000 rows. Each row is a customer entry and each column specifies data about the customer (column 1 first name, column 2 last name, column 3 email, column 4 city and so on). The 2nd excel file only contains 1 column with 4000 email addresses in it. These are email addresses of people who opted out of our newsletter and other marketing channels.

    Now I want to have excel delete all the rows of the first file that correspond to the email addresses of the 2nd file. Obviously the email addresses in both files are identical, though the 2nd one contains fewer entries and only those people we don't want to continue sending emails to.

    Could some excel master step out and show me the light? I would truly appreciate this as I've spent a bunch of time and nerves already googling around but the advice given usually only applies if the data of both excel files is the same.

    Thank you very much,
    John M.

  2. #2
    Registered User
    Join Date
    05-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Duplicates (2 files with different layout)

    Copy the 4000 rows from the second file into the first file and set it up to align on the same column as the e-mail address

    Next Conditional Formatting >> Highlight Cell Rules >> Duplicate Values...this will result in all duplicates color coded

    Next set up the filter to filter by color

    Delete all the rows in one go

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2301 (Build 16012.20000) Win 11 Home 64 Bit
    Posts
    22,319

    Re: Duplicates (2 files with different layout)

    I would add a column to the first worksheet. Insert a vlookup function to find the email in the second file.

    After finding all the first files with emails in the second file, sort the first file on the new vlookup/email column.

    All the records with information in the new column will be grouped together. Delete those rows in one swoop.

    Alan
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    06-04-2013
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Duplicates (2 files with different layout)

    Thanks jubiesxl and alansidman, for the quick replies!

    I tried your suggestion, jubiesxl, and it worked the way I wanted to. I just did a slight change because once I tried to "filter by color", excel for some reason would just color the whole email column in red. Instead, I selected "sort by color" and that did the trick.

    Thank you very much again Problem solved!

  5. #5
    Registered User
    Join Date
    05-17-2013
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    49

    Re: Duplicates (2 files with different layout)

    Glad it worked for you john.miller...

    I was thinking of all the combination (formulas, functions, codes etc), but then took a step back and asked myself wwjd...intuition got me going the way of the above posted solution

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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