Matching data in 2 cells in Excel

By Patricia Egen • June 13th, 2013

Today’s quick blog article will talk about a favorite formula string I use time and time again.  If you read my blog, you know I use Excel as my tool of choice for data manipulation.

My challenge today was to find and match emails in two columns.  What I wanted to do was weed out emails that were duplicated in both columns.   I used a formula and will explain it below.

Let’s use the following list as my example:

Column A Column B Column C
email1@text.com Email1@Text.com
test@test.com Bill@brown.com
Info@energy.cc Sally@egenconsulting.com
Bill@brown.com Fred@bedrock.com

The formula I would use on the sample above would be:

=IF(ISERROR(MATCH(A1,$C$1:$C$4,0)),””,A1)

I place the formula in the first row of Column B and copy it to the end of the rows to match Column A.  In my example I want to know which email in Column A is also in Column B.

Explaining the formula, the part of the formula describing the C column should start with the first row and end with the last row.  For example, let’s say column A had 20 rows and column C had 40 rows.  Then, the formula would read as follows:

=IF(ISERROR(MATCH(A1,$C$1:$C$40,0)),””,A1)

Note that the section describing the C column reads $C$40 which defines the range of data in the C Column.

What I end up with after inserting this formula in my example is the following:

Column A Column B Column C
email1@text.com  email1@text.com Email1@Text.com
test@test.com Bill@brown.com
Info@energy.cc Sally@egenconsulting.com
Bill@brown.com  Bill@brown.com Fred@bedrock.com

I can now sort the data together and remove the items in Column A.  Quick way to find matching data.  I was doing an import in my ACT database and I didn’t want to import records I already had. In this case it was emails, but it could be names, addresses, etc.  You get the idea.

Note: the data in column C does not have to be sorted.

 

Leave a Comment

« | Home | »

  • Been sent a support request?

    Click here to enter your ticket number: Ticket

  • Our Business

    Our Mission
    Bridging the gap between technology and business processes. Provide quality business consulting, with an emphasis on technology, maintain superior client relationships and enjoy the trust and respect of our clients

  • Us in a nutshell

    Swiftpage
    CRM applications
    Data Conversion
    Custom Programming
    SQL Experts
    Lotus Notes
    Quotewerks
    Stonefield Query
    MS Office Programming
    Business analysis
    Process Workflow
    Social Media and SEO
    Linux, Mainframe
    Been there, done that



  • Facebook Like Us!