如何检查三列是否在Excel中具有相似的值 [英] How to check if three columns have similar like value in Excel

问题描述

我在下面提到了Excel工作表.

I have below mentioned excel sheet.

ID  column1    column2     column3
1   Apple      apple - x   Apple - le
2   Mango      banana      cat
3   Dog        DOG - A     DOG - B

我想匹配column1，column2和column3的字符串(在这三列中至少有一个单词是相似的.)

I want to match string of column1, column2 and column3 (where atlease one word is similar in these three column).

必需的输出:

ID  column1    column2     column3     Status
1   Apple      apple - x   Apple - le  True
2   Mango      banana      cat         False
3   Dog        DOG - A     DOG - B     True

推荐答案

LO BO 的想法的代码.

在带有 VBA 的Excel中，尝试以下用户定义函数:

In Excel with VBA, try the following User Defined Function:

Public Function ThreeWay(s1 As String, s2 As String, s3 As String) As Boolean
    Dim a As String, b As String, c As String

    a = Split(s1, " ")(0)
    b = Split(s2, " ")(0)
    c = Split(s3, " ")(0)
    ThreeWay = False
    If a = b And a = c Then ThreeWay = True
End Function

EDIT#1:

对于仅两列，请使用:

Public Function TwoWay(s1 As String, s2 As String) As Boolean
    Dim a As String, b As String, c As String

    a = Split(s1, " ")(0)
    b = Split(s2, " ")(0)
    TwoWay = False
    If a = b Then TwoWay = True
End Function

EDIT#2:

要忽略大小写差异，请尝试:

To ignore case differences, try:

Public Function ThreeWayIC(s1 As String, s2 As String, s3 As String) As Boolean
    Dim a As String, b As String, c As String

    a = Split(UCase(s1), " ")(0)
    b = Split(UCase(s2), " ")(0)
    c = Split(UCase(s3), " ")(0)
    ThreeWayIC = False
    If a = b And a = c Then ThreeWayIC = True
End Function

这篇关于如何检查三列是否在Excel中具有相似的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！