基于另一个列中包含字符串中带有分隔符的值来对Excel中的列进行分类;其中字符串可以有两个单独的单词 [英] How to categorise a column in excel based on another column containing value in string with delimiter ; where string can have two separate words

问题描述

此问题是以前查询的后续操作：

= 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）））， ）

我只将 ISNUMBER（SEARCH（B $ 15，$ C $ 1：$ C $ 11））部分更改为 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）））

添加 IF 语句说如果搜索将单个单词作为单元格中的第一个字符来接受该搜索到arra y，否则以开始搜索; 。

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.

解决方案

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.

这篇关于基于另一个列中包含字符串中带有分隔符的值来对Excel中的列进行分类;其中字符串可以有两个单独的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！