Find a specific "text" in a column and group it in to a "Group"

[Dinesh_Excel]

Hi Friends,

I need your help for finding a specific "text" in a range and then categorize them in to a group.I tried using if then else statement but it extends to a very long formula.

For eg :if the input column contains "offline" then the ouput should be "Device offline"
similarly in the input column has" not in service", "out of service","out of order" should come under "Device not in Service"

Input Output
Offline Device Offline
Not in service Device Not in Service
Out of Service Device Not in Service
Out of Order Device Not in Service
Black screen Display Issues
Blue screen Display Issues
Frozen screen Display Issues
Bad LCD Display Issues
CID Display Issues
Will not boot Reboot Issues
Activation failure Device failure
Initialization failure Device failure
Application load error Device application error
No video output error Device application error
System failure Device application error
Application run error Device application error
Broken Screen Device damaged
Water Device damaged
Broken base Device damaged
Hinge issue Device damaged
Top Cover Device damaged
Coin vault removed Coin related issue
Coin accepter Coin related issue

 

[candybg]

A lookup table would be perfect for this!
List your possibilities for groupings like not in service", "out of service","out of order" and in the next column place your GROUP name like "Device not in Service".
Then use either INDEX/MATCH or VLOOKUP to return the group for each line. You can then use a Pivot Table to aggregate the data.

Hope this helps!

 

[Dinesh_Excel]

Thanks Candybg....but not sure if I have explained you correctly in my previous post ...I tried using the below formula but some how some tinkering would be of immense help for me.

I have a column "B" which has some texts and we need to find specific text in column "D".And the ouptut would be in column "C".


I tried this...=IF(OR(ISNUMBER(FIND($D$2:$D$34,B2))),"Yes","No")




Attached sheet for your reference..

 

[Deepak]

Is this...

Your formula is correct but need to notice two points.

• It should be entered by Ctrl+Shift+Enter
• You have wrong results due to blanks cells inputted with a space[Like as cells D2].

Check this...

=IFERROR(INDEX($D$2:$D$34,MATCH(TRUE,INDEX(FIND(TRIM($D$2:$D$34),B2)>1,,),0)),"NO")


Or With Ctrl+Shift+Enter

=IFERROR(INDEX($D$2:$D$34,MATCH(TRUE,FIND(TRIM($D$2:$D$34),B2)>1,0)),"NO")

 

[Dinesh_Excel]

Thank you Deepak...Its working fine now.
Appreciate your timely help.