+ Reply to Thread
Results 1 to 7 of 7

Filter a column containing sentences with a column of words

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2011 Mac
    Posts
    25

    Filter a column containing sentences with a column of words

    Hi

    I have a column of sentences in A, and a list of words in column B. I want the sentences in column A to appear in another target column (for example column C) if a they contain a word (not case sensitive) that is in the list of words in column B, and I do not want the same sentence to appear more than once in the target column.

    Any ideas?

    Thanks

    Njaal, Norway
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Filter a column containing sentences with a column of words

    Do you want the qualifying sentences to list in Col_C on the same row as the sentence?
    Or do you want to list the qualifying sentences in Col_C in the order that they appear, with no gaps?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Filter a column containing sentences with a column of words

    I got as far as

    C2:

    =IF(SUMPRODUCT((ROWS($B$2:$B$4)*(COUNTIF(A2,"*"&$B$2:$B$4&"*"))))>0,A2,"")

    and copied down

    I'm sure there's a more elegant solution.

    Maybe:

    =IF(OR(ISNUMBER(SEARCH($B$2:$B$4,A2))),A2,"")

    entered as an array with Ctr+Shift+Enter
    Last edited by daffodil11; 01-16-2014 at 11:40 AM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Filter a column containing sentences with a column of words

    @daffodil11

    For that approach, this regular formula would also work...(without the C+S+E):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-16-2014
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2011 Mac
    Posts
    25

    Re: Filter a column containing sentences with a column of words

    Hi

    Tanks for the quick response. I wanted the qualifying sentences in Col_C in the order that they appear, with no gaps. I tried your suggestions but keep getting formula contains error. I have excel 2011 for mac (sorry).

    Thanks.

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Filter a column containing sentences with a column of words

    Using your posted workbook...
    This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) begins the list of sentences that contain at least one of the lookup words
    Please Login or Register  to view this content.
    Copy C2 and paste into C3:C5

    The results will be:
    I had a little farm
    I did not have a little goose
    It did not snow yesterday

    Is that some thing you can work with?

  7. #7
    Registered User
    Join Date
    01-16-2014
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2011 Mac
    Posts
    25

    Re: Filter a column containing sentences with a column of words

    I`ll check it out, thanks a lot

    Njaal

+ 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. Remove Stop Words from a column containing 16000 rows of sentences
    By Abhayrajify in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-30-2013, 07:14 PM
  2. Replies: 18
    Last Post: 11-29-2012, 07:43 AM
  3. Counting words in one column to count words in another column
    By stuart1133 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2010, 05:54 AM
  4. List out words from sentences
    By ap_naveen in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-21-2010, 06:58 AM
  5. Split sentences into words
    By sparx in forum Excel General
    Replies: 5
    Last Post: 02-22-2006, 03:51 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