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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
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)))
 

divyasn31

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
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
 

divyasn31

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

divyasn31

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,228
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 15, 2021
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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, ",", ".", "/".
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,676
Members
425,229
Latest member
Rashid mahmood

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
Top