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
Bill HendersonWeb MarketingAsked:
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.

Rgonzo1971Commented:
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

Open in new window


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

Open in new window

Regards
0
Rob HensonFinance 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
aikimarkCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill HendersonWeb 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?


Thanks for your time

Bill
0
aikimarkCommented:
@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, ":")
            dicCategories.Add strParsed(0), strParsed(1)
        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

Open in new window

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
Bill HendersonWeb 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
Rob HensonFinance 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

Your issues matter to us.

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

Start your 7-day free trial
aikimarkCommented:
are there any Hard Drive items that are NOT HDD?
are there any Battery items that are not BATT?
0
Bill HendersonWeb 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
aikimarkCommented:
are there any OTHER values (not HDD) that are in the Hard Drive category?
0
Bill HendersonWeb MarketingAuthor Commented:
No - any HDD are Hard Drives.

I think I just mistook your meaning of 1:1.
0
aikimarkCommented:
So, why not just move the second word into the destination cell and use that as the category?
0
aikimarkCommented:
1. Select your source cells
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
Bill HendersonWeb 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
aikimarkCommented:
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, ":")
            dicCategories.Add strParsed(0), strParsed(1)
        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

Open in new window

0
Bill HendersonWeb 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.