Finding and returning a text from list from numerous identifyer given

[Shinianne]

Good day kind sir/madams, im in great need of help. I was given a big file for reporting. My dilemma is that i couldnt figure out a formula to where i can search a cell will multiple text and return it with a title from my list.

Below is the image from what im working on. The first cell contains the short description. Thats where i need to extract a word and return it with a value, the second column shows the configuration item where i need it categorized i have a longe list more that 200 to 500. Now will it be possible to dig and extract a text from column A and return a value to column B.

I deal with this like 3000 cells and i have to manually do it.. i was wondrin if theres an easier way to do it.

Like if the first cell shows Token it would return with RSA.. only if possible.. ill be in forever debt if this works. Thank you so much

 

[nardagus]

Hey,

Do I get it right that:

If cell A27 contains info about faulty Plantronics you want to put Hardware in B27?

I don't think it is doable. You need to create some dictionary, and even then I'm not sure if it will be much of a help. Question is if you are able to create such a dic containing all possibilities which will spare you qualitative analysis...

 

[Eric W]

Welcome to the forum.

Not sure if this is what you're looking for:

	A	B	C	D	E	F	G	H	I
Short Description	Configuration Item	Key word	Configuration item
Active	Active Directory
RSA : Authentication issue /	Benefit	BenefitConnect
Active Dierctory - Password reset	Active Directory	Desktop	laptop issue
Benefit Connect - Unable to access site - page cannot be displayed	BenefitConnect	Laptop	laptop issue
Desktop Issue-Running on Low space	laptop issue	Excel	Excel
Excel - Timesheet tool is disappeared	Excel	Outlook	Outlook
F5 VPN - Unable to login	RSA	VPN	RSA
Install Software- Request to install SQL EXPR	Software Installation Request	Install	Software Installation Request
Outlook-Unable to open	Outlook
VPN - unable to login to Cisco anyconnect	RSA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=LOOKUP(2,1/(($G$2:$G$15<>"")*SEARCH($G$2:$G$15,A3)),$H$2:$H$15)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Your sample seems to show some discrepancies. I think it will be tricky to set up your key word table, but it should help.

 

[Shinianne]

Unfortunately i tried this but it gets too many matches ...

 

[Shinianne]

Thats a good idea .. ill try to make a dictionary see if that helps