+ Reply to Thread
Results 1 to 4 of 4

Comparing two columns of text

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    Comparing two columns of text

    Hello Excel community!
    Thank you so much for clicking on my question - I have found this site to be very helpful and informative.
    On the attached, you will find four columns of text data. Columns A:B are from one system, and columns D:E are from a different system. I need a quick way to compare the data in columns A:B to the data in column D:E. and then compare the data D:E to A:B.

    As you can see, columns A and D are Sales Managers and columns B and E are Sales Reps. I need to be able to specifically "call out" or identify where I have discrepancies between the two.
    Can someone help me with a quick and intuitive approach? Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Comparing two columns of text

    this is one way (attached). First I did a concatenate of AB in col C =A3&B3 auto populated down.
    then same in col F for D3&E3.
    then a vlookup against each.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Comparing two columns of text

    thank you so much for your help!
    Unfortunately, I'm looking for a more intuitive approach rather than a concatenate and vlookup as this causes many N/A returned.
    for instance, if you look at Lorabi, Boubakeur who reports to Accomazzo, David. In your solution, it shows no match; however, if you just look at the data, there is a match between
    the two columns although it is not a perfect text match as one column has Mr. in front and the other does not.

    Is there a more intuitive solution?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,879

    Re: Comparing two columns of text

    I have used power query. I did a left join and then a right join. You can review the Mcode in the attached file.
    Attached Files Attached Files
    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

+ 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. Comparing two text columns
    By Calluum2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2016, 08:34 AM
  2. [SOLVED] IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns
    By jrochet in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-22-2012, 11:06 AM
  3. Excel 2007 : Comparing and matching two text columns
    By Bizzielizzie in forum Excel General
    Replies: 2
    Last Post: 04-11-2012, 09:50 AM
  4. Comparing columns of text and calculating a value
    By andy_27 in forum Excel General
    Replies: 12
    Last Post: 11-02-2009, 11:19 AM
  5. Comparing text columns
    By kpmunt in forum Excel General
    Replies: 2
    Last Post: 09-29-2008, 10:06 PM
  6. Comparing Two Columns of Text
    By sailortigger in forum Excel General
    Replies: 2
    Last Post: 06-21-2005, 03:11 PM
  7. [SOLVED] [SOLVED] Comparing text in columns
    By Lear in forum Excel General
    Replies: 1
    Last Post: 06-08-2005, 04:35 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