How to use keyword to tag every row automatically

[KellyWills]

Hi All,

I have a large set of data that requires me to tag each row based on 2 column. I think there's possibility of using IF but I'm not sure how to joint 2 column. Here's my listing:

The colored column is my intended outcome. Is there a possibility to get this outcome by using excel functions?

Thank you in advance

 

[Peter_SSs]

Here is another function that you could try.

Function TagsAdd(rLookup As Range, rVals As Range) As String
  Dim varLookup As Variant
  Dim c As Range
  Dim s As String
  Dim i As Long
  
  varLookup = rLookup.Value
  For Each c In rVals
    s = s & " " & c.Value
  Next c
  s = s & " "
  For i = 1 To UBound(varLookup)
    If Len(varLookup(i, 2)) > 0 Then
      If InStr(1, s, " " & varLookup(i, 2) & " ", vbTextCompare) > 0 Then
        If InStr(1, TagsAdd & ",", ", " & varLookup(i, 1) & ",", vbTextCompare) = 0 Then TagsAdd = TagsAdd & ", " & varLookup(i, 1)
      End If
    End If
  Next i
  TagsAdd = Mid(TagsAdd, 3)
End Function

My 'Tags' sheet is the same as in the last post.

'Data' sheet

KellyWills.xlsm
	R	S	T
1
2	I love to eat apple	I like to drink juice	A, D
3	I like pineapple
4	I love starfruit		B
5	I Like to eat apple	I like to drink water	A, E
6	I love strawberry	I love milk	C, F
7	Horses like straw	Berry is nice
8	I like juice	Tom likes milk & juice	D, F
Data

Cell Formulas
Range	Formula
T2:T8	T2	=TagsAdd(Tags!A$2:B$12,R2:S2)

 

[KellyWills]

Having the data on two sheets should not be a problem. The question is what are the sheets and where is the data on them?
Your formula above indicates that the lookup table of tags/fruit is in columns A:B of sheet 'Tags' but post #1 indicates they are in columns A:B of sheet 'Listing'.
The above formula indicates the values to look in are in columns R:S of the sheet that the formula is on, post 1 has them in A:B

So, here is my sample workbook as I think you have it based on the post 9 formula.

Sheet Tags

KellyWills.xlsm
	A	B
1	Tags
2	A	Apple
3	B	Starfruit
4	C	Strawberry
5	D	Juice
6	E	Water
7	F	Milk
8
9
10
11
12
Tags

Sheet Data (but the name of this sheet is irrelevant)

KellyWills.xlsm
	R	S	T
1
2	I love to eat apple	I like to drink juice	A, D
3	I like pineapple
4	I love starfruit		B
5	I Like to eat apple	I like to drink water	A, E
6	I love strawberry	I love milk	C, F
7	Horses like straw	Berry is nice
8	I like juice	Tom likes milk & juice	D, F
Data

Cell Formulas
Range	Formula
T2:T8	T2	=AddTags(Tags!$A$2:$B$12,R2:S2)

If all of that seems correct and it is still not working for you then one possibility might be a Windows v Mac issue. I don't use a Mac so I cannot test in that environment. I am also not particularly familiar with Windows/Mac issues.

Can you confirm that the formula is resulting in a blank cell and no error message is received?

Hi Peter,

I have tested out in Windows but it shows me #VALUE! error instead of blanks like in Mac. From your code I did change these item in green according to my column name.

Function AddTags(rLookup As Range, rVals As Range) As String
  Dim Foods As Variant, Tags As Variant, itm As Variant
  Dim sTag As String
  Dim pos As Variant
 
  Tags = Application.Transpose(rLookup.Columns(1).Value)
  Foods = Application.Transpose(rLookup.Columns(2).Value)
  For Each itm In Split(Join(Application.Index(rVals.Value, 1, 0)))
    pos = Application.Match(itm, Foods, 0)
    If IsNumeric(pos) Then
      sTag = Tags(pos)
      If InStr(1, AddTags & ",", ", " & sTag & ",", vbTextCompare) = 0 Then AddTags = AddTags & ", " & sTag
    End If
  Next itm
  AddTags = Mid(AddTags, 3)
End Function

 

[Peter_SSs]

From your code I did change these item in green according to my column name.

If you want to format vba code your own way in the forum (eg green) then you need to use the "RICH" code tags, not the "VBA" code tags. I have changed that for you this time.

What did you change them to? Can you post the actual code that you used?

Can you also post some small sample data (both sheets) like in post #1 so the we can see the the sheet names and what rows/columns the data is in?
When you do the sheet with the actual formula, select the first formula cell so that we can see the formula in the formula bar.

 

[KellyWills]

If you want to format vba code your own way in the forum (eg green) then you need to use the "RICH" code tags, not the "VBA" code tags. I have changed that for you this time.

What did you change them to? Can you post the actual code that you used?

Can you also post some small sample data (both sheets) like in post #1 so the we can see the the sheet names and what rows/columns the data is in?
When you do the sheet with the actual formula, select the first formula cell so that we can see the formula in the formula bar.

Hi Peter,

Thank you for changing it to RICH, I didn't know. I have successfully altered your code to fit my data. Here's the code that I'm using

Function AddTags(rLookup As Range, rVals As Range) As String
  Dim Keyword As Variant, Tag As Variant, itm As Variant
  Dim sTag As String
  Dim pos As Variant
  
  Tag = Application.Transpose(rLookup.Columns(1).Value)
  Keyword = Application.Transpose(rLookup.Columns(2).Value)
  For Each itm In Split(Join(Application.Index(rVals.Value, 1, 0)))
    pos = Application.Match(itm, Keyword, 0)
    If IsNumeric(pos) Then
      sTag = Tag(pos)
      If InStr(1, AddTags & ",", ", " & sTag & ",", vbTextCompare) = 0 Then AddTags = AddTags & ", " & sTag
    End If
  Next itm
  AddTags = Mid(AddTags, 3)
End Function

I works fine on my Mac. However, I noticed there are some rows are not tagged according to Tag list. For example, referring to your post #10, row 2 shows the tag in column T but if I have the same exact data in row 550, column T returns blank instead of A, D tag.

 

[Peter_SSs]

I have not been able to reproduce that problem. I am now using the code from post #14 and here are my sheets

KellyWills.xlsm
	A	B
1	Tags
2	A	Apple
3	B	Starfruit
4	C	Strawberry
5	D	Juice
6	E	Water
7	F	Milk
8
Tags

KellyWills.xlsm
	R	S	T
1
2	I love to eat apple	I like to drink juice	A, D
3	I like pineapple
4	I love starfruit		B
5	I Like to eat apple	I like to drink water	A, E
6	I love strawberry	I love milk	C, F
7	Horses like straw	Berry is nice
8	I like juice	Tom likes milk & juice	D, F
9
549
550	I love to eat apple	I like to drink juice	A, D
Data

Cell Formulas
Range	Formula
T2:T9,T549:T550	T2	=AddTags(Tags!$A$2:$B$12,R2:S2)

 

[KellyWills]

I have not been able to reproduce that problem. I am now using the code from post #14 and here are my sheets

KellyWills.xlsm
	A	B
1	Tags
2	A	Apple
3	B	Starfruit
4	C	Strawberry
5	D	Juice
6	E	Water
7	F	Milk
8
Tags

KellyWills.xlsm
	R	S	T
1
2	I love to eat apple	I like to drink juice	A, D
3	I like pineapple
4	I love starfruit		B
5	I Like to eat apple	I like to drink water	A, E
6	I love strawberry	I love milk	C, F
7	Horses like straw	Berry is nice
8	I like juice	Tom likes milk & juice	D, F
9
549
550	I love to eat apple	I like to drink juice	A, D
Data

Cell Formulas
Range	Formula
T2:T9,T549:T550	T2	=AddTags(Tags!$A$2:$B$12,R2:S2)

Hi Peter,

I think I figured why I have the error. For example in your post #15, if I change the column B in Tag sheet into more than 2 words, it won't work. For example, instead of just Apple, the keyword is Apple Juice. I'm not sure where in the code should I change this. My actual data of keyword may be a single to 3 words.

Thank you.

 

[Peter_SSs]

My actual data of keyword may be a single to 3 words.

Then that is quite a different scenario.

Could we have a new small but representative set of sample data and expected result (with XL2BB so that we can easily copy for testing)?

 

[KellyWills]

Then that is quite a different scenario.

Could we have a new small but representative set of sample data and expected result (with XL2BB so that we can easily copy for testing)?

I see, this is my first trying to use it. Hopefully it is correct.

Kelly Sample.xlsx
	A	B
1	Factors	Keyword
2	A	Apple
3	A	Epal
4	A	Apple Juice
5	B	Pineapple
6	C	Starfruit
7	D	Juice
8	E	Water
Factors

The reason why there's 3 A is because Apple & Epal can be tag as the same category.

Kelly Sample.xlsx
	R	S	T
1			Expected tags outcome
2	I love to eat apple	I like to drink juice	A, D
3	I love apple juice	I love pineapple	A, B
4	I like to drink water	I love starfruit	C, E
5	I like both apple and pineapple	I like to drink water	A, B, E
Data

 

[Peter_SSs]

Thanks for the sample. Just to be sure: Can you confirm that row 3 does not also get a 'D' tag even though it contains the word "juice"?

 

[KellyWills]

Thanks for the sample. Just to be sure: Can you confirm that row 3 does not also get a 'D' tag even though it contains the word "juice"?

Yes, correct