+ Reply to Thread
Results 1 to 3 of 3

How to compare partial match in two columns

  1. #1
    Registered User
    Join Date
    08-25-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    1

    How to compare partial match in two columns

    I want to compare two columns as shown below. Column A includes a list of film titles and the releasing years, which are in brackets. Column B includes a list of film titles without releasing years. I want to find out whether films in column B also show up in column A. If a film in column B is included in column A, then column C is recorded 1; otherwise 0.

    I can't use the "if(iserror(match" formula, since the films in column B are not exactly the same as films in column A. Column A includes the releasing year while column B doesn't. In this case, how should I solve the problem?

    Column A
    A1: I Am Legend (2007)
    A2: Fast Food Nation (2004)
    A3: Transformers (2000)

    Column B
    B1: I am legend
    B2: Crash
    B3: Transformer

    Column C
    C1: 1
    C2: 0
    C3: 1

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: How to compare partial match in two columns

    If it is a simple one to one match, IE
    Cells A1 and B1 will always have the same film then try this:

    In Cell C1 enter:
    =IF($B1=0," ",IF(ISERROR(FIND(LOWER($B1),LOWER($A1),1)),0,1))

    If the film names can be located anywhere in column B, then
    you will need to do additional work.

  3. #3
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Rowley
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: How to compare partial match in two columns

    Here is a better formula.
    If the film names are located anywhere in column B, this formula should find them:

    In Cell C1 enter:
    =IF(ISERROR(MATCH(LEFT(A1,FIND("(",A1,1)-2),$B$1:$B$150,0)),0,1)

    Drag the formula down as many rows as needed, it is currently set at 150, change to suit your needs.

    The film names in column A MUST have the date following the name in the format (xxxx)
    the formula looks for the first parenthesis, if it's not there, it fails.

+ 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