[VBA] Create categories in Excel including or excluding certain keywords?

[ottame]

Hi, I've just signed up because I'm struggling with a categorization problem and hope that I can find some help here.

I need to categorize a list of items, depending on whether they contain some keywords and at the same time do not contain others. These are the categories that I need to create, together with the rules ("Contiene" means contains and "No contiene" means does not contain):

The category "ENCIMERAS", for example, should group all items that contain "encimera" and that at the same time do not contain "cocina", "cuarzo", "granito" or "gris".
On the other hand, the category "ENCIMERAS DE COCINA" should include all items that contain "encimera" and also "cocina" (not necessarily in this order) but that do not contain "cuarzo", "granito" or "gris".
This is a sample of the elements I'm working with and the expected outcome:

Due to the filters I'm using, not every element will fall in a category, some of them will remain uncategorized, I am aware, it's not an error.
I've tried to achieve the categorization with a regular expression, but couldn't find an expression that can include some keywords and exclude some others at the same time.

I thought that maybe it could be achieved with VBA, but I don't know where to start. My guess is that i should create an expression like this:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">IF [Consulta] CONTAINS "encimera" AND "cocina"
AND NOT CONTAINS "cuarzo" OR "granito" OR "gris"
THEN "ENCIMERAS DE COCINA"
ELSEIF....
ELSE "OTROS"

</code>How do I translate contains and does not contain into VBA language? And, more important, is it even possible to do such thing?

If you want to see and manipulate the sample data, I've put them in a spreadsheet: google-speadsheet-sample-data

Thanks in advance,

 

[Akuini]

Hi, I've just signed up because I'm struggling with a categorization problem and hope that I can find some help here.

I need to categorize a list of items, depending on whether they contain some keywords and at the same time do not contain others. These are the categories that I need to create, together with the rules ("Contiene" means contains and "No contiene" means does not contain):

I thought that maybe it could be achieved with VBA, but I don't know where to start. My guess is that i should create an expression like this:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">IF [Consulta] CONTAINS "encimera" AND "cocina"
AND NOT CONTAINS "cuarzo" OR "granito" OR "gris"
THEN "ENCIMERAS DE COCINA"
ELSEIF....
ELSE "OTROS"

</code>

You can use InStr function, like this:

For Each r In somerange
    If InStr(1, r, "encimera", 1) > 0 And InStr(1, r, "cocina", 1) > 0 And InStr(1, r, "cuarzo", 1) = 0 _
        And InStr(1, r, "granito", 1) = 0 And InStr(1, r, "gris", 1) = 0 Then
        r.Offset(, 1) = "ENCIMERAS DE COCINA"
    ELSEIF....
    
    End If
Next

To learn about InStr function:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function

 

[ottame]

You can use InStr function, like this:

For Each r In somerange
    If InStr(1, r, "encimera", 1) > 0 And InStr(1, r, "cocina", 1) > 0 And InStr(1, r, "cuarzo", 1) = 0 _
        And InStr(1, r, "granito", 1) = 0 And InStr(1, r, "gris", 1) = 0 Then
        r.Offset(, 1) = "ENCIMERAS DE COCINA"
    ELSEIF....
    
    End If
Next

To learn about InStr function:
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function

Thank you very much, it worked!

 

[Akuini]

Thank you very much, it worked!

Your welcome & thanks for replying