# Column of single words as filter for columns with sentences

1. ## 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.

2. ## 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

3. ## 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. ## 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

5. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Column of single words as filter for columns with sentences

Good deal. Thanks for the feedback!

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

#### 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