Column of single words as filter for columns with sentences

**[email protected]** · 01-20-2014, 03:52 PM

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.

**AlKey** · 01-20-2014, 04:20 PM

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

**azumi** · 01-20-2014, 09:51 PM

=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

**Tony Valko** · 01-21-2014, 10:42 AM

@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

**Tony Valko** · 01-21-2014, 10:47 AM

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.

**[email protected]** · 01-21-2014, 04:11 PM

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

**AlKey** · 01-21-2014, 04:38 PM

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

**[email protected]** · 01-21-2014, 05:47 PM

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

**AlKey** · 01-21-2014, 05:58 PM

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.

**Tony Valko** · 01-23-2014, 10:52 AM

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)));"")

**[email protected]** · 01-23-2014, 11:21 AM

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

**[email protected]** · 01-23-2014, 11:25 AM

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

God bless

**Tony Valko** · 01-23-2014, 12:11 PM

Good deal. Thanks for the feedback!

Column of single words as filter for columns with sentences

LinkBack

Thread Tools

Rate This Thread

Display

Column of single words as filter for columns with sentences

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter a column containing sentences with a column of words

Remove Stop Words from a column containing 16000 rows of sentences

[SOLVED] How to manage number of words in sentences?

List out words from sentences

Split sentences into words

Bookmarks

Bookmarks

Posting Permissions