+ Reply to Thread
Results 1 to 6 of 6

Comparing two text columns and return data based on an exact match

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Comparing two text columns and return data based on an exact match

    Hi,

    First, thank you in advance for your assistance. I've got two columns with over 3500 entries and would like to compare the text strings in Column 1 with Column 2 for an exact match. If there's an exact match, I'd like to return the values found in Columns 3 and 4 into new Columns 5 and 6. I believe I can do this using the match and index functions. With my actual data, I may also need to use wildcards since the data contains multiple sentences in the cell.
    I'd appreciate it if someone can assist with this. Thank you!

    Example below:

    Column 1 Column 2 Column 3 Column 4
    cat orange red sky
    dog doggie blue cloud
    tiger kangaroo green sun
    elephant cow orange moon
    mouse cat one star
    apple lion two rain
    pear tiger three thunder
    orange dog four snow

    The first entry in new columns 5 and 6 should return "one" and "star" respectively.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Comparing two text columns and return data based on an exact match

    Hello and welcome to the forum,

    In my example, I used vlookup to accomplish this in the first group. In the second group, I assumed that column 2 was a sentence and used wildcards. Let me know if this is what you are after.

    abousetta

    P.S. Do a google search for my name and Advanced VLookup and you find other great examples as I have a thread dedicated to this topic.
    Attached Files Attached Files
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Comparing two text columns and return data based on an exact match

    Quote Originally Posted by abousetta View Post
    Hello and welcome to the forum,

    In my example, I used vlookup to accomplish this in the first group. In the second group, I assumed that column 2 was a sentence and used wildcards. Let me know if this is what you are after.

    abousetta

    P.S. Do a google search for my name and Advanced VLookup and you find other great examples as I have a thread dedicated to this topic.
    Thank you for your assistance. This is close to what I'm trying to accomplish. Looking at your example with the wildcard formulas, I need to have an exact match. If the formula is comparing "cat" and then it must be a perfect match to "cat", not "cat and dog". Also, the actual data that I'm working with contains a paragraph in each cell. Is there a limit to the size of the cell that Excel can handle when trying to accomplish this task?

    Thank you again!

  4. #4
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Comparing two text columns and return data based on an exact match

    Sorry, you lost me...

    If the cell is matching cat (column A) to cat (column B) then you would use the first example of vlookup without the wildcards. If cat in Column B is part of a paragraph then how would we know that it's not "cat and dog" or "catastrophic event"?

    I don't see the rule here

    abousetta

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Comparing two text columns and return data based on an exact match

    As for your second question, MS says that the length of cell contents (text) is 32,767 characters, but only 1,024 will display in a cell; all 32,767 display in the formula bar. That was for 2003, and I think they now display all 32K characters in the cell.

    abousetta

  6. #6
    Registered User
    Join Date
    04-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Comparing two text columns and return data based on an exact match

    Sorry, I didn't do a good job of explaining what I was trying to accomplish. Let me start again. I took your feedback and created a sample spreadsheet. For the most part, it works. However, I'm not sure why Row 5 doesn't show a match. It should be identical, since it's a cut and paste. I thought that might be due to a character limitation and why I asked the question regarding character limitations.



    The other, more confusing question is actually part 2 of what I'm trying to do. My data list is a combination of exact matches and some that another colleague slightly modified. For instance, if the original cell contained three sentences, he may have eliminated one of the sentences, leaving the other two in tact. After running through the first pass of exact matches, I was hoping to use the wildcard formula to help me identify the modified cells. In my previous post, I combined my two goals and that was probably the confusing piece.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Comparing two text columns and return data based on an exact match

    First part (for now)... Excel can't process more than 255 characters in a formula. There you have to tell it to only look at the first 255 characters or it errors out.

    To make matters worse, when comparing, you can't use a standard formula, but rather an array formula because now each time you compare you have to only look at the first 255 characters in a cell. The new formula looks like this:

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

    entered as Ctrl + Shift + Enter, not just Enter. If you do this correctly then you will get curly brackets around your formula indicating it's an array formula.

    Warning... array formulas take more time to process and so if you have a lot of them then the workbook can become very slow.

    Second part... you will need to go fuzzy. Alan has a great piece of code for a user-defined fuzzy formula. Google his name and fuzzy formula and Excel and it should come up. It's in MrExcel.com.

    Good luck.

    abousetta

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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