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]

I don't use a Mac but some methods that I might have used for this are not available on that platform so I think that I would approach it like this. Perhaps not ideal but the best I can see at the moment.

On the Factors sheet I would add a formula column as shown

KellyWills_1.xlsm
	A	B	C
1	Factors	Keyword	Words
2	A	Apple	1
3	A	Epal	1
4	A	Apple Juice	2
5	B	Pineapple	1
6	C	Starfruit	1
7	D	Juice	1
8	E	Water	1
9	E	Very Hot Water	3
Factors

Cell Formulas
Range	Formula
C2:C9	C2	=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

Then sort that table by the Words column - Descending - to look like this.
This sorting may well mean that the Factors in column A do not stay grouped together - eg the 'E' factors in this example

KellyWills_1.xlsm
	A	B	C
1	Factors	Keyword	Words
2	E	Very Hot Water	3
3	A	Apple Juice	2
4	A	Apple	1
5	A	Epal	1
6	B	Pineapple	1
7	C	Starfruit	1
8	D	Juice	1
9	E	Water	1
Factors

Cell Formulas
Range	Formula
C2:C9	C2	=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

Now use this UDF

Function DoTags(rLookup As Range, rVals As Range) As String
  Dim a As Variant
  Dim sVals As String, CurrTags As String
  Dim i As Long
  
  sVals = " " & Join(Application.Index(rVals.Value, 1, 0), " | ") & " "
  a = rLookup.Value
  For i = 1 To UBound(a)
    If InStr(1, CurrTags & ",", " " & a(i, 1) & ",", 1) = 0 Then
      If InStr(1, sVals, " " & a(i, 2) & " ", 1) > 0 Then
        CurrTags = CurrTags & ", " & a(i, 1)
        sVals = Replace(sVals, " " & a(i, 2) & " ", " ", 1, -1, 1)
      End If
    End If
  Next i
  DoTags = Mid(CurrTags, 3)
End Function

KellyWills_1.xlsm
	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

Cell Formulas
Range	Formula
T2:T5	T2	=DoTags(Factors!A$2:B$9,R2:S2)

 

[KellyWills]

I don't use a Mac but some methods that I might have used for this are not available on that platform so I think that I would approach it like this. Perhaps not ideal but the best I can see at the moment.

On the Factors sheet I would add a formula column as shown

KellyWills_1.xlsm
	A	B	C
1	Factors	Keyword	Words
2	A	Apple	1
3	A	Epal	1
4	A	Apple Juice	2
5	B	Pineapple	1
6	C	Starfruit	1
7	D	Juice	1
8	E	Water	1
9	E	Very Hot Water	3
Factors

Cell Formulas
Range	Formula
C2:C9	C2	=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

Then sort that table by the Words column - Descending - to look like this.
This sorting may well mean that the Factors in column A do not stay grouped together - eg the 'E' factors in this example

KellyWills_1.xlsm
	A	B	C
1	Factors	Keyword	Words
2	E	Very Hot Water	3
3	A	Apple Juice	2
4	A	Apple	1
5	A	Epal	1
6	B	Pineapple	1
7	C	Starfruit	1
8	D	Juice	1
9	E	Water	1
Factors

Cell Formulas
Range	Formula
C2:C9	C2	=LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1

Now use this UDF

Function DoTags(rLookup As Range, rVals As Range) As String
  Dim a As Variant
  Dim sVals As String, CurrTags As String
  Dim i As Long
 
  sVals = " " & Join(Application.Index(rVals.Value, 1, 0), " | ") & " "
  a = rLookup.Value
  For i = 1 To UBound(a)
    If InStr(1, CurrTags & ",", " " & a(i, 1) & ",", 1) = 0 Then
      If InStr(1, sVals, " " & a(i, 2) & " ", 1) > 0 Then
        CurrTags = CurrTags & ", " & a(i, 1)
        sVals = Replace(sVals, " " & a(i, 2) & " ", " ", 1, -1, 1)
      End If
    End If
  Next i
  DoTags = Mid(CurrTags, 3)
End Function

KellyWills_1.xlsm
	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

Cell Formulas
Range	Formula
T2:T5	T2	=DoTags(Factors!A$2:B$9,R2:S2)

Do I need to change anything in the UDF if I place “Words” column in the first column instead of the third?

What is the purpose of sorting? Does it gives any impact in the result?

Thank you.

 

[Peter_SSs]

Do I need to change anything in the UDF if I place “Words” column in the first column instead of the third?

No, this would require no change in the UDF. When you use the UDF in the worksheet though it would require a change. Instead of, say
=DoTags(Factors!A$2:B$9,R2:S2)
you would need to use something like
=DoTags(Factors!B$2:C$9,R2:S2)

What is the purpose of sorting? Does it gives any impact in the result?

Yes, it does impact the results. The purpose is to ensure that the longest multi-word phrases are search for first. If we don't do that with the method that I have used, then you might get a 'D' for 'juice' in "I love apple juice" instead of or as well as an 'A'. That's why I asked the question I did in post #19.