+ Reply to Thread
Results 1 to 6 of 6

Find similar values in two columns

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    13

    Find similar values in two columns

    Hello excelforum,
    I'm hoping you can help me. I have a spreadsheet with two sheets. On one sheet I have a list of over 300 companies in the first column. The subsequent columns on that sheet have address, email, phone, etc. On the second sheet I have only one column with 60 of those companies. I would like to filter and delete all but those 60 companies from the first sheet. The problem is the company names on the second sheet are not formatted exactly the same. They are almost identical, but the names have extra spaces. A lot of the companies have similar names, which might complicate it. I thought about using find and replace to remove the spaces, but not all of them have the extra spaces. Whats the best way to do this? I am attaching a sample file. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Find similar values in two columns

    I inserted a new column A on sheet 1
    then a simple trim formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To remove any spare spaces

    Then I inserted a formula in cell B2 of sheet 2

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I found two matches in your data. How many would you expect?
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Find similar values in two columns

    This is a tricky one....Would like to see this play out.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    08-07-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find similar values in two columns

    Thank you for the fast response mehmetcik! So simple, and almost worked perfectly. Out of the 60 I matched 56. I have another sheet with 42 companies which matched 35. If there is no other way, this will definitely help. I suppose I could filter the matches and grab the remaining 11 manually. Should I leave this open, or is that as close as I'll get?

  5. #5
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Find similar values in two columns

    You could possibly use a formula a above sand then match the first ten characters, then match last ten characters and then the middle ten characters.

    Any Match could then be checked manually.

    It is up to you how much effort you want to put into it.

  6. #6
    Registered User
    Join Date
    08-07-2012
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find similar values in two columns

    Sounds good. Thank you so much for your help!

+ 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. Match values from two columns having similar values in 1st column if not error
    By csunilkumar in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2014, 08:32 AM
  2. Replies: 1
    Last Post: 04-30-2014, 05:49 PM
  3. Replies: 5
    Last Post: 10-26-2013, 01:03 PM
  4. Search columns with similar values
    By vijay2482 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2010, 08:18 AM
  5. Compare columns for similar values
    By uncleslinky in forum Excel General
    Replies: 1
    Last Post: 04-07-2009, 02:08 AM
  6. code required to find similar entries in two columns
    By Duguid1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2008, 09:52 AM
  7. FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES
    By Kir in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2005, 05:45 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