How to Assign specific value to a clumn based on text in another column using VBA?

[divyasn31]

Hi, Could someone help me with the VBA code to assign a specific value to a column ("Category") based on the value in another column ("Item")?
For example:
Sheet Name: Source Data
Column Headers: Product ID (Col. A)
Items (Col. B)
Store (Col. C)
Category (Col. D)

The "category" is assigned based on the occurrence of the first word in "Items". Below are the criteria:

Item​	Category ​
Apple	Fruit
Mangoes	Fruit
Orange	Fruit
Carrot	Vegetable
Eggplant	Vegetable
Cola	Drink
Smoothie	Drink
Sprite	Drink

Now, if cell B2 (Item)= "Apple, Mangoes, and Cola", then I want cell D2 (Category)="Fruit". Since Apple is the first word in B2 that matches the Criteria above.
if cell B3= "Cola, Apples, and Cakes", then I want D2= "Drink". Since Cola is the first word in B3 and the category is Drink based on the above table.
if Cell B4="Cakes, Lemons, Apple, Cola, and Smoothie", I want D2= "Fruit". In this case, we don't have cakes or lemons in the above table, the next word is Apple which we already have on our table. Therefore I want D2 as Fruit.

 

[Peter_SSs]

Welcome to the MrExcel board!

1. Does the Item/category list in post #1 exist in the workbook somewhere?
2. Do you really need a macro as I think the following formula does what you want?

divyasn31.xlsm
	B	C	D	E	F	G	H
1	Items	Store	Category			Item	Category
2	Apple, Mangoes, and Cola		Fruit			Apple	Fruit
3	Cola, Apples, and Cakes		Drink			Mangoes	Fruit
4	Cakes, Lemons, Apple, Cola, and Smoothie		Fruit			Orange	Fruit
5						Carrot	Vegetable
6						Eggplant	Vegetable
7						Cola	Drink
8						Smoothie	Drink
9						Sprite	Drink
10
Source Data

Cell Formulas
Range	Formula
D2:D4	D2	=LET(m,MATCH(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(B2,"and ",""),", ","</c><c>")&"</c></p>","//c"),G$2:G$9,0),INDEX(H$2:H$9,INDEX(FILTER(m,ISNUMBER(m)),1)))

 

[divyasn31]

Welcome to the MrExcel board!

1. Does the Item/category list in post #1 exist in the workbook somewhere?
2. Do you really need a macro as I think the following formula does what you want?

divyasn31.xlsm
	B	C	D	E	F	G	H
1	Items	Store	Category			Item	Category
2	Apple, Mangoes, and Cola		Fruit			Apple	Fruit
3	Cola, Apples, and Cakes		Drink			Mangoes	Fruit
4	Cakes, Lemons, Apple, Cola, and Smoothie		Fruit			Orange	Fruit
5						Carrot	Vegetable
6						Eggplant	Vegetable
7						Cola	Drink
8						Smoothie	Drink
9						Sprite	Drink
10
Source Data

Cell Formulas
Range	Formula
D2:D4	D2	=LET(m,MATCH(FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(B2,"and ",""),", ","</c><c>")&"</c></p>","//c"),G$2:G$9,0),INDEX(H$2:H$9,INDEX(FILTER(m,ISNUMBER(m)),1)))

Hi Peter, Thanks for your response. Yes, the items/category table is in the second Sheet called Item Categorisation List. And yes. i really need a macro for this as the list can be sometimes really big. Also when ever I use complex/ long excel formulas the spreadsheet stops responding.

 

[Peter_SSs]

Hi Peter, Thanks for your response. Yes, the items/category table is in the second Sheet called Item Categorisation List. And yes. i really need a macro for this as the list can be sometimes really big. Also when ever I use complex/ long excel formulas the spreadsheet stops responding.

OK, assuming the lookup list is in cols A:B, starting in row 2 of Item Categorisation List, then try this with a copy of your workbook.

Sub LookupCategory()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Item Categorisation List")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With Sheets("Source Data")
    a = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      For Each itm In Split(Replace(a(i, 1), " and ", "", , 1, 1), ",")
        If d.exists(Trim(itm)) Then
          b(i, 1) = d(Trim(itm))
          Exit For
        End If
      Next itm
    Next i
    .Range("D2").Resize(UBound(b)).Value = b
  End With
End Sub

 

[divyasn31]

OK, assuming the lookup list is in cols A:B, starting in row 2 of Item Categorisation List, then try this with a copy of your workbook.

Sub LookupCategory()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Item Categorisation List")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With Sheets("Source Data")
    a = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      For Each itm In Split(Replace(a(i, 1), " and ", "", , 1, 1), ",")
        If d.exists(Trim(itm)) Then
          b(i, 1) = d(Trim(itm))
          Exit For
        End If
      Next itm
    Next i
    .Range("D2").Resize(UBound(b)).Value = b
  End With
End Sub

Hi Peter, Thanks for that. I'll try this code and let you know.

 

[divyasn31]

OK, assuming the lookup list is in cols A:B, starting in row 2 of Item Categorisation List, then try this with a copy of your workbook.

Sub LookupCategory()
  Dim d As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Sheets("Item Categorisation List")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  With Sheets("Source Data")
    a = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      For Each itm In Split(Replace(a(i, 1), " and ", "", , 1, 1), ",")
        If d.exists(Trim(itm)) Then
          b(i, 1) = d(Trim(itm))
          Exit For
        End If
      Next itm
    Next i
    .Range("D2").Resize(UBound(b)).Value = b
  End With
End Sub

Hi Peter, The code works perfectly for the sample data I mentioned. But it doesn't work when the Item value is, say "Eggplant is in the list". In this case, I want the category as Vegetable. Is there a way wherein the code extracts each word in the cell and compares it with the Categories list and whichever word first appears, the category gets assigned?

 

[Peter_SSs]

That is because in your original samples all the relevant words were separated by a comma or the word "and".
The issue that usually arises with this sort of question is determining where a 'word' starts and ends. The basic concept is that words are what comes between spaces and/or the beginning/end of the text. However, when punctuation gets involved, that is not the case. For example, your original example "Apple, Mangoes, and Cola" the only words that fit that method are "and" and "Cola".

So, my first questions (could be more following) are:
Will your text contain any punctuation at all?
If so, is there a limited list of the punctuation characters or could it be anything? eg ? , . : ; ' ! " etc

Another issue relating to "words" is that I notice in your original sample of Items you have "apple" (singular) and "mangoes" (plural)
Could "apples" (plural) or "mango" (singular) turn up in the text you are assessing and if so are you going to list singular and plural of all items in the lookup list?

 

[divyasn31]

That is because in your original samples all the relevant words were separated by a comma or the word "and".
The issue that usually arises with this sort of question is determining where a 'word' starts and ends. The basic concept is that words are what comes between spaces and/or the beginning/end of the text. However, when punctuation gets involved, that is not the case. For example, your original example "Apple, Mangoes, and Cola" the only words that fit that method are "and" and "Cola".

So, my first questions (could be more following) are:
Will your text contain any punctuation at all?
If so, is there a limited list of the punctuation characters or could it be anything? eg ? , . : ; ' ! " etc

Another issue relating to "words" is that I notice in your original sample of Items you have "apple" (singular) and "mangoes" (plural)
Could "apples" (plural) or "mango" (singular) turn up in the text you are assessing and if so are you going to list singular and plural of all items in the lookup list?

Hi Peter, thanks for your reply. There might be punctuations but not to worry about singular/plural as I'll be adding them in the category list if needed.

 

[Peter_SSs]

There might be punctuations

In that case, what about my follow-on question?

If so, is there a limited list of the punctuation characters or could it be anything? eg ? , . : ; ' ! " etc

Your answer also raises some further issues. I'm wondering if your sample data is actually like your real data.
Since there could be punctuation, if there might be, say, apostrophes, are they part of the sentence punctuation or are they actually part of a word?

eg "Ann O'Connor likes Coca-Cola's flavour - mostly"
Here, the first apostrophe is part of the whole word O'Connor whereas the second one is not part of Coca-Cola or Cola (in my opinion)
Similar issue with the two hyphens in the sentence.

 

[divyasn31]

In that case, what about my follow-on question?


Your answer also raises some further issues. I'm wondering if your sample data is actually like your real data.
Since there could be punctuation, if there might be, say, apostrophes, are they part of the sentence punctuation or are they actually part of a word?

eg "Ann O'Connor likes Coca-Cola's flavour - mostly"
Here, the first apostrophe is part of the whole word O'Connor whereas the second one is not part of Coca-Cola or Cola (in my opinion)
Similar issue with the two hyphens in the sentence.

Based on the example you shared, I would consider O'Connor as one word and Coca-Cola also as one word. Basically based on my requirement a new word is separated by- space, ",", ".", "/".