# Assign values to cell if part of another cell matches condition

HI,

I have a huge list of convoluted values that are detailed code for different pieces of PC hardware. a couple dummy examples would be

SPS-DRV HDD 750GB SATA
SPS BATT 6C 62WHR
SPS-DRV HDD 640GB
SPS BATT 13D 41WHR

and so on...

Now what I really need from this are simpler values I can use to bucket-ize these parts into larger categories.

So I need a formula that can say if cell X contains "HDD" then cell X+1 should display "Hard Drive" OR if cell X contains "BATT" then cell X+1 should display "Battery"

I have approximately 10 categories that I can identify through a contains clause such as BATT or HDD.

Is there a way to do this via excel formula or even VBA?

Thank you

Bill
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi,

I find it easier with VBA

pls try
``````Function CategoryType(Product As String) As String
If InStr(1, Product, "BATT") Then
CategoryType = "Battery"
ElseIf InStr(1, Product, "HDD") Then
CategoryType = "Hard Drive"
Else
CategoryType = "Not valid"
End If
End Function
``````

or when more than one per product

``````Function CategoryType(Product As String) As String
Dim strList As String
If InStr(1, Product, "BATT") Then
strList = strList & "Battery" & ", "
End If
If InStr(1, Product, "HDD") Then
strList = strList & "Hard Drive" & ", "
End If

If strList <> "" Then
CategoryType = Left(strList, Len(strList) - 2)
Else
CategoryType = "Not Valid"
End If
End Function
``````
Regards
0
Finance AnalystCommented:
You can use the FIND function to determine whether a particular string is included in the text.

With xl2003 and prior you can only nest 7 IF statements but I believe this is no longer the case with xl2007 and later. However, you can get round it in xl2003 by not nesting but closing each IF statement with blank or zero so that the end result is a string of blanks with a text value within it or a sum of zeroes and a true value.

As a result, you can then include the multiple IFs within a VLOOKUP so that the result is then found in a table giving the expanded text value.

Example:

=VLOOKUP(IF(ISERROR(FIND("HDD",A1,1)),0,1)+IF(ISERROR(FIND("BATT",A1,1)),0,2),Table,2,FALSE)

Using the above example, finding HDD in the string would return 1, finding BATT would return 2. The lookup would then find 1 or 2 in the table of expanded text and return the relevant result.

Hope that makes sense.

Thanks
Rob H
0
Commented:
Are there any other entries that would be Battery or Hard Drive?  If not, then there really isn't a need to "categorize" the items as they have a 1:1 relationship with their categories.
0
Web MarketingAuthor Commented:
Hi RGonzo - so I'm not very strong with VBA.

If my column name is Old_Part_Desc and I want to insert the category value in the cell to the right of each value being analyzed, how would I modify your code? I tried:

Function CategoryType(Product As String) As String
If InStr(1, Old_Part_Desc, "BATT") Then
CategoryType = "Battery"
ElseIf InStr(1, Old_Part_Desc, "HDD") Then
CategoryType = "Hard Drive"
Else
CategoryType = "Not valid"
End If
End Function

Is this going to go through the full list in the column labeled Old_Part_Desc?

And can I keep adding ElseIfs to evaluate per row if I have, say, 10 different possible categories based on 10 different criteria?

Bill
0
Commented:
@billium99

You would use the Rgonzo1971 function in a formula in the column where you want the category to appear.

You should also test this function:
``````Public Function Q_28230013(parmPart)
Static strParsed() As String
Static dicCategories As Object
Static vItem As Variant
If dicCategories Is Nothing Then
Set dicCategories = CreateObject("scripting.dictionary")
For Each vItem In Array("HDD:Hard Drive", "BATT:Battery")
strParsed = Split(vItem, ":")
Next
End If
strParsed = Split(parmPart, " ", 3)
If dicCategories.Exists(strParsed(1)) Then
Q_28230013 = dicCategories(strParsed(1))
Else
Q_28230013 = "No Category"
End If
End Function
``````
You add strings to the Array() function, rather than code up VBA statements.

Its cell formula usage is the same as the Rgonzo1971 function.
0
Web MarketingAuthor Commented:
Hi Aikimark,

These are not 1:1.

In the sample I gave I tried to show that there are many variations, but the various values would all contain the value I seek:

Column 1 - Old_Part_Desc            Column 2 - Category

SPS-DRV HDD 750GB SATA             Hard Drive (contained HDD)
SPS BATT 6C 62WHR                     Battery (contained BATT)
SPS-DRV HDD 640GB                     Hard Drive (contained HDD)
SPS BATT 13D 41WHR                   Battery (contained BATT)

Does that make sense?

Where Column 1 is named Old_Part_Desc
0
Finance AnalystCommented:
See attached simple workbook with simpler suggestion than the nested IFs suggested earlier.

For additional parts add the abbreviation in row 2, copy across the formula in row 1 and add the details to the table in columns I to K, numbering sequentially in column I.

Columns B to E can be hidden using the outline icon in the top margin or physically hide the columns.

Thanks
Rob H
Part-desc-check.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
are there any Hard Drive items that are NOT HDD?
are there any Battery items that are not BATT?
0
Web MarketingAuthor Commented:
Aikimark,I thought you meant 1:1 like every HDD would be SPS-DRV HDD 640GB but there are many variations that trigger "Hard Drive".

But yes any value containing HDD = "Hard Drive".

I'm trying Rob's solution now.

Bill
0
Commented:
are there any OTHER values (not HDD) that are in the Hard Drive category?
0
Web MarketingAuthor Commented:
No - any HDD are Hard Drives.

I think I just mistook your meaning of 1:1.
0
Commented:
So, why not just move the second word into the destination cell and use that as the category?
0
Commented:
2. Data | Text to Columns
3. Select all but the second column and click on the Do Not Import radio button
4. Change the destination to the start of your target column
5. Click Finish
0
Web MarketingAuthor Commented:
Sometimes it's not the second word, and the final values - Hard Drive, instead of HDD, have to be particular values for other things I'm doing with this data downstream.

Thanks.

I think Rob's solution will work - just looking for the time to implement...
0
Commented:
Here is a table-driven version that does not assume the second word is the word to be matched.  For best performance, arrange the items in the Array() in most-to-least frequent order.
``````Public Function Q_28230013(parmPart)
Static strParsed() As String
Static dicCategories As Object
Static vItem As Variant
If dicCategories Is Nothing Then
Set dicCategories = CreateObject("scripting.dictionary")
For Each vItem In Array("HDD:Hard Drive", "BATT:Battery")
strParsed = Split(vItem, ":")
Next
End If
Q_28230013 = "No Category"
For Each vItem In dicCategories
If InStr(1, parmPart, vItem, vbBinaryCompare) <> 0 Then
Q_28230013 = dicCategories(vItem)
Exit For
End If
Next
End Function
``````
0
Web MarketingAuthor Commented:
Thanks for the help!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.