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

 

[KOKOSEK]

Book1
	A	B	C	D	E	F	G
1	Tags	Desc		Name	fav 1	fav 2	TAGS
2	A	Apple		Ally	I love to eat apple	I like to drink juice	A, D
3	B	Starfruit		Adam	I love starfruit		B
4	C	Strawberry		Kelly	I Like to eat apple	I like to drink water	A, E
5	D	juice		Karen	I love strawberry	I love milk	C, F
6	E	Water
7	F	Milk
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=TEXTJOIN(", ",1,IFERROR(INDEX(A:A,SORT(UNIQUE(IF(ISNUMBER(SEARCH($B$2:$B$7,E2&F2)),ROW($B$2:$B$7),10^10)))),""))

 

[KellyWills]

Book1
	A	B	C	D	E	F	G
1	Tags	Desc		Name	fav 1	fav 2	TAGS
2	A	Apple		Ally	I love to eat apple	I like to drink juice	A, D
3	B	Starfruit		Adam	I love starfruit		B
4	C	Strawberry		Kelly	I Like to eat apple	I like to drink water	A, E
5	D	juice		Karen	I love strawberry	I love milk	C, F
6	E	Water
7	F	Milk
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=TEXTJOIN(", ",1,IFERROR(INDEX(A:A,SORT(UNIQUE(IF(ISNUMBER(SEARCH($B$2:$B$7,E2&F2)),ROW($B$2:$B$7),10^10)))),""))

Thank you for your reply! Works like wonders!

Do you mind to explain how the formula works so that I could tailor it in future? Like why 10^10? I don't quite understand that.

Btw, is there possibility of removing duplicates? For example F2 shows apple juice & the outcome will be A, A, D. I'd like to remove the duplicates.

Thank you

 

[KellyWills]

Thank you for your reply! Works like wonders!

Do you mind to explain how the formula works so that I could tailor it in future? Like why 10^10? I don't quite understand that.

Btw, is there possibility of removing duplicates? For example F2 shows apple juice & the outcome will be A, A, D. I'd like to remove the duplicates.

Thank you

Btw, following questions, if there alternatives for Unique function that can be substitute? I will be sharing the file with Excel 2016 user - where unique function isn’t available :/

 

[KOKOSEK]

Btw, is there possibility of removing duplicates? For example F2 shows apple juice & the outcome will be A, A, D. I'd like to remove the duplicates.

Did you try? It shouldn't be duplicates.

 

[KOKOSEK]

Btw, following questions, if there alternatives for Unique function that can be substitute? I will be sharing the file with Excel 2016 user - where unique function isn’t available :/

That's why everybody loves 365 (for UNIQUE, FILTER, LET....). Gives us a chance. Maybe somebody adapt it into older version.

 

[Peter_SSs]

Works like wonders!

Are you sure?
Below I have used that formula in column G. Check the coloured rows as I don't believe you would want those results?

For your Excel 365 I have provided what I believe to be a more robust formula in column H.

If you want this to work in 2016 then I think you will need to use vba as 2016 worksheet functions do not lend themselves to "textjoin" type operations.
You could try this user-defined function & use it as in column I below.

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

KellyWills.xlsm
	A	B	C	D	E	F	G	H	I
1	Tags
2	A	Apple			I love to eat apple	I like to drink juice	A, D	A, D	A, D
3	B	Starfruit			I like pineapple		A
4	C	Strawberry			I love starfruit		B	B	B
5	D	Juice			I Like to eat apple	I like to drink water	A, E	A, E	A, E
6	E	Water			I love strawberry	I love milk	C, F	C, F	C, F
7	F	Milk			Horses like straw	Berry is nice	C
8					I like juice	Tom likes milk & juice	D, F	D, F	D, F
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=TEXTJOIN(", ",1,IFERROR(INDEX(A:A,SORT(UNIQUE(IF(ISNUMBER(SEARCH($B$2:$B$7,E2&F2)),ROW($B$2:$B$7),10^10)))),""))
H2:H8	H2	=TEXTJOIN(", ",1,FILTER(A$2:A$7,ISNUMBER(SEARCH(" "&B$2:B$7&" "," "&E2&" "&F2&" ")),""))
I2:I8	I2	=AddTags(A$2:B$7,E2:F2)

 

[KOKOSEK]

I did not think about 'part' of tags word, like apple in pineapple or fruit in starfruit.
Simple tweak in main part in formula:

=TEXTJOIN(", ",1,IFERROR(INDEX(A:A,SORT(UNIQUE(IF(ISNUMBER(SEARCH(" "&$B$2:$B$7&" "," "&E2&" "&F2&" ")),ROW($B$2:$B$7),10^10)))),""))

as in yours in H2, made a job.

 

[KellyWills]

Are you sure?
Below I have used that formula in column G. Check the coloured rows as I don't believe you would want those results?

For your Excel 365 I have provided what I believe to be a more robust formula in column H.

If you want this to work in 2016 then I think you will need to use vba as 2016 worksheet functions do not lend themselves to "textjoin" type operations.
You could try this user-defined function & use it as in column I below.

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

KellyWills.xlsm
	A	B	C	D	E	F	G	H	I
1	Tags
2	A	Apple			I love to eat apple	I like to drink juice	A, D	A, D	A, D
3	B	Starfruit			I like pineapple		A
4	C	Strawberry			I love starfruit		B	B	B
5	D	Juice			I Like to eat apple	I like to drink water	A, E	A, E	A, E
6	E	Water			I love strawberry	I love milk	C, F	C, F	C, F
7	F	Milk			Horses like straw	Berry is nice	C
8					I like juice	Tom likes milk & juice	D, F	D, F	D, F
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=TEXTJOIN(", ",1,IFERROR(INDEX(A:A,SORT(UNIQUE(IF(ISNUMBER(SEARCH($B$2:$B$7,E2&F2)),ROW($B$2:$B$7),10^10)))),""))
H2:H8	H2	=TEXTJOIN(", ",1,FILTER(A$2:A$7,ISNUMBER(SEARCH(" "&B$2:B$7&" "," "&E2&" "&F2&" ")),""))
I2:I8	I2	=AddTags(A$2:B$7,E2:F2)

Hi Peter,

Thank you for your VBA Code. I've tried to incorporate it with my excel but it seems not working (it shows blank). My tags are in different sheet, do I need to modify the code? my formula is already:

=AddTags(Tags!$A$2:$B$12,R2:S2)

Thank you

 

[Peter_SSs]

My tags are in different sheet, do I need to modify the code? my formula is already:

=AddTags(Tags!$A$2:$B$12,R2:S2)

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?