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

divyasn31

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
AppleFruit
MangoesFruit
OrangeFruit
CarrotVegetable
EggplantVegetable
ColaDrink
SmoothieDrink
SpriteDrink

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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
BCDEFGH
1ItemsStoreCategoryItemCategory
2Apple, Mangoes, and ColaFruitAppleFruit
3Cola, Apples, and CakesDrinkMangoesFruit
4Cakes, Lemons, Apple, Cola, and SmoothieFruitOrangeFruit
5CarrotVegetable
6EggplantVegetable
7ColaDrink
8SmoothieDrink
9SpriteDrink
10
Source Data
Cell Formulas
RangeFormula
D2:D4D2=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)))
 
Upvote 0
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
BCDEFGH
1ItemsStoreCategoryItemCategory
2Apple, Mangoes, and ColaFruitAppleFruit
3Cola, Apples, and CakesDrinkMangoesFruit
4Cakes, Lemons, Apple, Cola, and SmoothieFruitOrangeFruit
5CarrotVegetable
6EggplantVegetable
7ColaDrink
8SmoothieDrink
9SpriteDrink
10
Source Data
Cell Formulas
RangeFormula
D2:D4D2=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.
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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.
 
Upvote 0
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.

VBA Code:
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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, ",", ".", "/".
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top