+ Reply to Thread
Results 1 to 13 of 13

Column of single words as filter for columns with sentences

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

    Column of single words as filter for columns with sentences

    Hi

    I have excel for mac 2011

    I have a column of sentences ( A ) and a column of words ( B ). I want a new column ( C ) in which sentences from A are copied onto only if they contain one or more of the words in column B.

    I want the sentences in column C to appear with no cells in between them.

    I have tried these formulas:

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

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

    =IF(COUNT(INDEX(SEARCH($B$2:$B$4,A2),0)),A2,"")

    =IFERROR(INDEX(A:A,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(TRANSPOSE($B$2:$B$4),$A$2:$A$5)),ROW($A$2:$A$5)),ROW($A$2:$A$5)),ROW($A$2:$A$5)),ROWS($2:2))),"")


    but i keep getting "formula contains error". Any ideas for a solution?


    Regards,
    Njaal, Norway.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Column of single words as filter for columns with sentences

    Hi and welcome to the forum,

    Try this array formula
    **Must be confirmed with Ctrl+Shift+Enter key combination.

    =IFERROR(INDIRECT("A"&SMALL(IFERROR(IF(SEARCH($B$2:$B$5,$A$2:$A$5)>0,ROW($2:$5),""),""),ROW(1:1))),"")

    A
    B
    C
    1
    Results
    2
    I had a little farm farm I had a little farm
    3
    I did not have a little goose did not I did not have a little goose
    4
    I received an e-mail snow It did not snow yesterday
    5
    It did not snow yesterday
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,244

    Re: Column of single words as filter for columns with sentences

    =IF(OR(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE($B$2:$B$4," ",REPT(" ",99)),1+99*(ROW(OFFSET($A$1,,,1+LEN($B$2:$B$4)-LEN(SUBSTITUTE($B$2:$B$4," ",""))))-1),99)),A2)))=TRUE,A2,"-")

    Array Formula and copied down

    Azumi

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Column of single words as filter for columns with sentences

    @AlKey

    Hmmm...

    Using your formula these are the results I get...

    Data Range
    A
    B
    C
    2
    I did not have a little farm
    farm
    I did not have a little farm
    3
    I did not have a little goose
    did not
    I did not have a little goose
    4
    This is a little mess
    snow
    5
    It did not snow yesterday
    little
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Column of single words as filter for columns with sentences

    Try this...

    Data Range
    A
    B
    C
    2
    I did not have a little farm
    farm
    I did not have a little farm
    3
    I did not have a little goose
    did not
    I did not have a little goose
    4
    I received an e-mail
    snow
    It did not snow yesterday
    5
    It did not snow yesterday
    little


    This array formula** entered in C2 and copied down until you get blanks:

    =IFERROR(INDEX(A:A,SMALL(IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(B$2:B$5),A$2:A$5)),ROW(B$2:B$5))>0,ROW(A$2:A$5)),ROWS(C$2:C2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

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

    Re: Column of single words as filter for columns with sentences

    Hi thanks for all the input

    I keep getting formula contains error on all the suggestions above (i.e., I get no output, just "formula contains error"), is it perhaps because because mac for excel requires something unorthodox in the formula?

    thanks, Njaal

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Column of single words as filter for columns with sentences

    You getting an error because these are array formulas. **Must be confirmed with Ctrl+Shift+Enter key combination.

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

    Re: Column of single words as filter for columns with sentences

    Yeah I got that part, but I still keep getting the formula contains error thing. I copy the formula into C2, then I hit Crtl+Shift+Enter, then I get "formula contains error", then the As after =IFERROR(INDEX are highlighted in blue

    =IFERROR(INDEX(A:A,SMALL(IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(B$2:B$5),A$2:A$5)),ROW(B$2:B$5))>0,ROW(A$2:A$5)),ROWS(C$2:C2))),"")

    Am I hitting Crtl+Shift+Enter wrong?

    Thanks

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Column of single words as filter for columns with sentences

    Ok, let's try this.

    1. Copy and paste formula in a cell and press Enter.
    2. Press and release F2 key on your keyboard and then press and hold Ctrl and Shift together and then hit Enter.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Column of single words as filter for columns with sentences

    I have read that Mac's use a different key combo for array entering (and some do use CTRL, SHIFT, ENTER). Something like CMD RETURN ? Does that sound familiar?

    Also, some international locations use semicolons as the argument separator. Have you tried replacing the commas with semicolons?

    =IFERROR(INDEX(A:A;SMALL(IF(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(B$2:B$5);A$2:A$5));ROW(B$2:B$5))>0;ROW(A$2:A$5));ROWS(C$2:C2)));"")

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

    Re: Column of single words as filter for columns with sentences

    Hi

    Great, the semicolon thing worked, but the results I get is only "I had a little farm", even when I copy it down. IŽll see if I get it to work, in the meantime thanks for the input

    Njaal

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

    Re: Column of single words as filter for columns with sentences

    Hey I figured it out with another formula thanks a thousand for the help

    God bless

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Column of single words as filter for columns with sentences

    Good deal. Thanks for the feedback!

+ 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. [SOLVED] Filter a column containing sentences with a column of words
    By njaalgw@gmail.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-17-2014, 10:41 AM
  2. 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
  3. [SOLVED] How to manage number of words in sentences?
    By emil9216 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-01-2013, 04:25 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