If cell A1 contains (shirt,hat, or pants) then display (clothes) in B1 , If cell A1 contains (frog,snake,or lizard) then display (reptile) in B1 etc.

[bvalkenb]

Please help. I have been researching and have been unable to find the correct solution.

Situation:

Column A has 1000's of qualitative survey responses. I would like to label / categorize the survey responses in a corresponding cell.

For example:

If Cell A1 contains: "shirt","pants","hat", then display "clothes" in B2

If Cell A1 contains: "sun", moon", "stars", then display "space" in B2

This formula needs to continue on because I have about 50 categories which will have keywords associated with them.

Thank you in advance for your suggestions.

 

[bvalkenb]

Seriously amazing Scott. Thank you so much.

 

[bvalkenb]

To take this formula one step further:

Is it possible to code one survey response under two categories? Currently, if a cell has two separate key words, it is labeled "#N/A".

For example.. Survey response 1: It is a starry night in July

Keyword table:

Starry - Space

July - Season

So formula outcome would be: Space, Season

This might be a stretch, and even if I just continue to use the current formula, I am beyond happy that you could help with such a seemingly tough problem.

If though this would be a simple edit to the formula it would prove very much a time-saver in coding the thousands of responses.

 

[Snakehips]

bvalkenb,

Here is a rough and ready possibility for a response satisfying two categories......

Excel 2007
	A	B	C	D	E	F	G	H	I
1	I see the sun wearing a hat	space, clothes						shirt	clothes
2	moon pants are cool	space, clothes						pants	clothes
3	No blue shirt	clothes						hat	clothes
4	stars are not blue	space						sun	space
5								moon	space
6								stars	space
Sheet8

Cell Formulas
Range	Formula
B1	{=IFERROR(LOOKUP(9.999999999E+307,MATCH("*"&$H$1:$H$6&"*",A1,0),$I$1:$I$6),"")&IF(SUM(IFERROR(MATCH("*"&$H$1:$H$6&"*",A1,0),0))>1,", "&INDEX($I$1:$I$6,MATCH(1,MATCH("*"&$H$1:$H$6&"*",A1,0),0),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

It requires confirming with Ctrl + Shift + Enter

Hope that helps.

 

[Snakehips]

Alternatively, a standard formula possibility ???

Excel 2007
	A	B	C	D	E	F	G	H	I
1	I see the sun wearing a hat	space, clothes						shirt	clothes
2	moon pants are cool	space, clothes						pants	clothes
3	No blue shirt	clothes						hat	clothes
4	stars are not blue	space						sun	space
5	pants pants pants pants sun	space, clothes						moon	space
6	sun sun sun pants	space, clothes						stars	space
7	sun stars	space, space
8
Sheet8

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(2,MATCH("*"&$H$1:$H$6&"*",A1,0),$I$1:$I$6),"")&IFERROR(LOOKUP(2,MATCH("*"&SUBSTITUTE($H$1:$H$6,LOOKUP(2,MATCH("*"&$H$1:$H$6&"*",A1,0),$H$1:$H$6),"§x§")&"*",A1,0),", "&$I$1:$I$6),"")

 

[bvalkenb]

Just got a chance to try this second formula; it works perfectly!!!

Thank you SO much Tony!!!

 

[bvalkenb]

Scott or Tony:

When I am using the above formula (last one posted), I run into one problem:

The formula codes short responses perfectly, but when the survey response exceeds ~200 characters, the formula returns no key words found in the comment. Do you know what might be the cause of this character limit in the survey response coding?

Thank you again,

Bo

 

[Snakehips]

Bo,

There is a character limit of 255 on the Lookup value within the MATCH function !!!!!

Try the below where I have used SEARCH instead of MATCH and you should be good for several thousand characters

Excel 2007
	A	B
1	Moon and pants	space, clothes
Sheet2

Cell Formulas
Range	Formula
B1	=IFERROR(LOOKUP(2,SEARCH("*"&$H$1:$H$6&"*",A1,1),$I$1:$I$6),"")&IFERROR(LOOKUP(2,SEARCH("*"&SUBSTITUTE($H$1:$H$6,LOOKUP(2,SEARCH("*"&$H$1:$H$6&"*",A1,1),$H$1:$H$6),"§x§")&"*",A1,1),", "&$I$1:$I$6),"")