Find content in a cell and have it = something else

[sam71]

Hi all,

Not sure if my title makes sense but an example might explain what I require a bit better

A1 contains:
<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=381><COLGROUP><COL style="WIDTH: 286pt; mso-width-source: userset; mso-width-alt: 13933" width=381><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 286pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=381>EVGA Intel X58FTW3 Motherboard LGA1366

B1 contains:
Motherboards

I need some code that will look at B1 "Motherboards" and
then if it matches a particular thing then it looks at A1 and the text and finds "LGA1366" and so giving me "Motherboards|LGA1366" in C1

I will have all categories such as "Motherboards|LGA1366" located on another workbook
</TD></TR></TBODY></TABLE>

Not sure if I have made sence but I need to do something as all of our suppliers have different category structures so we have to have our own! Some have just one category and no sub categories hense this thread as it will just make things too messy!

 

[crook_101]

Hi,

A formulaic approach is something along the lines of below:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">EVGA X*(&^^TF2 Motherboard LGA1366</td><td style=";">Motherboard</td><td style=";">Motherboard LGA1366</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">EVGA X*(&^^TF2 Motherboard LGA1367</td><td style=";">Processor</td><td style=";">No Match</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">B1,A1</font>)</font>),RIGHT(<font color="Red">A1,FIND(<font color="Green">B1,A1</font>)+3</font>),"No Match"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">B2,A2</font>)</font>),RIGHT(<font color="Red">A2,FIND(<font color="Green">B2,A2</font>)+3</font>),"No Match"</font>)</td></tr></tbody></table></td></tr></table><br />

HTH
Ian

 

[sulakvea]

hi sam, can you please be a lil more specific as to what the EVGA Intel X58FTW3 Motherboard LGA1366 statement may look like? is it always gonna be 5 words separated by 1 space between each? are they always gonna contain the word "motherboard" and you would need the description that follows the word to be added to the next column? the solution will greatly depend on the exact logic.

without knowing all those details, and based on the one line provided, here is what you can do in C1. will work if the logic is always the same:

=B1&" | " &RIGHT(A1,LEN(A1)-FIND("Motherboard",A1)-11)

 

[sam71]

Every line is different and not all require this to be done! It is only where there is a group of products that need to be separated into a subcategory! One supplier has them separated and an other doesn't, I just want it to be fairly neat!

I have added some more example.....

Also the Pipe is required to separate category|subcategory|subsubcategory etc.

 

[Aladin Akyurek]

Every line is different and not all require this to be done! It is only where there is a group of products that need to be separated into a subcategory! One supplier has them separated and an other doesn't, I just want it to be fairly neat!

I have added some more example.....

Also the Pipe is required to separate category|subcategory|subsubcategory etc.

Create the following keywords in column D...

LGA1366
LGA1155
Warranty
DDR2
DDR3

Now we can invoke...

=LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$6,X2),$C$2:$C$6)

which will categorize the product in X2.

 

[sam71]

Is there a way to have it find a specefic word or text from a column in one workbook and pull the result from another workbook which has all of the new categories listed?

i.e. it finds "LGA1366" and then pulls the result "Motherboards|LGA1366"


Just not sure how to explain what I actually want!