How to categorise a column in excel based on anoth

Question

This question is a follow up to a previous query: How to categorise a column in excel based on another column that contains value in string separated by semicolons

I have the following spreadsheet (please click on link below for image): Raw data and expected output

My question is I want to categorise the raw data so the output is as pictured in B16:B34 and C16:C34. I am trying to categorise people by their interests when their interests are in a column containing strings separated by semicolon with multiple words. The Name can come up multiple times according to their interests where in this case Movie, Action Movie, Music, Rock Music, Jazz Music and Radio.

I have tried the answer provided by @Glitch_doctor:

{=IFERROR(INDEX($B$1:$B$11,SMALL(IF(ISNUMBER(SEARCH(B$15,$C$1:$C$11)),ROW($C$1:$C$11)-ROW(INDEX($C$1:$C$11,1,1))+1),ROW(1:1))),"")}

The issue with the answer is that when a person's interest is actually Action Movie they show up in the Movie categorisation. I am trying to match when their interest is Action Movie they only show up in Action Movie after categorisation. Multiple words doesn't seem to work with SEARCH function.

I tried to replace the SEARCH function with a VBA code:

Function ProjectSearch(ByVal strProj As String, ByVal strVal As String, _
    Optional ByVal delimiter As String = ";") As Boolean

    Dim i As Long
    Dim strSplit() As String
    strSplit = Split(strProj, delimiter)
    ProjectSearch = False

    For i = LBound(strSplit) To UBound(strSplit)
        If strSplit(i) = strVal Then

            ProjectSearch = True
            Exit Function
        End If
    Next i
End Function

Since I am a newbie it doesn't seem to work. My question is there a way where I can do what I want without VBA? If I do need to use VBA what do I need to do?

Thanks kindly in advance.

Answer 1

Okay, that was much simpler than I was expecting it to be:

=IFERROR(INDEX($C$1:$C$11,SMALL(IF(ISNUMBER(IF(SEARCH(E$15,$D$1:$D$11)=1,SEARCH(E$15,$D$1:$D$11),SEARCH(CONCATENATE("; ",E$15),$D$1:$D$11))),ROW($D$1:$D$11)-ROW(INDEX($D$1:$D$11,1,1))+1),ROW(1:1))),"")

I only changed the ISNUMBER(SEARCH(B$15,$C$1:$C$11)) part to ISNUMBER(IF(SEARCH(E$15,$D$1:$D$11)=1,SEARCH(E$15,$D$1:$D$11),SEARCH(CONCATENATE("; ",E$15),$D$1:$D$11)))

The addition IF statement is saying that if the search finds the single word as the first character in the cell to accept that search into the array, otherwise search beginning with "; " instead.