Excel：检查列中是否存在单元格字符串值，并获取对该字符串的所有单元格引用

Question

我怀疑这可能是VBA的工作，这超出了我的能力。但情况是这样的：

表1（CAS1）中的A列包含x行文本值

第2页（CAS2）中的A列包含x行文本值

A部分-对于CAS1中的每个行值，我需要知道字符串是否包含在CAS2中的任何单元格中。不完全匹配，字符串只能是搜索单元格的一部分。

B部分-我需要知道CAS2中包含CAS1值的每个单元格的单元格值（如果它们确实存在，可以在CAS1中搜索的单元格旁边的单元格中列出）。

我尝试了以下方法来尝试A部分，但都没有成功：

vlookup(A1,sheet2!A:A,1,false)
NOT(ISNA(MATCH(A1,sheet2!A:A,0)))
ISNUMBER(MATCH(A1,sheet2!A:A,0))
COUNTIF(sheet2!A:A,A1)>0 
IF(ISERROR(MATCH(A1,sheet2!A:A, 0)), "No Match", "Match")

我知道CAS2中的一些单元格值包含CAS1中的单元格值，所以我不知道为什么它们返回false或不匹配。我怀疑这可能是由于文本内容的性质。以下是一些示例数据：

CAS1

LQ056
RV007H
RV008
RV009H
TSN304
TSN305

CAS2

RV009-satin-nickel-CO.jpg
STR314.jpg
STR315.jpg
HCY001.jpg
RV008-oval-rad-CO.jpg
HCY001-BRAC006.jpg

任何帮助都将不胜感激。

Answer 1

不需要VBA。一些简单的数组公式可以完成这项工作。

要查看CAS1中的条目是否存在于CAS2中：

=OR(ISNUMBER(SEARCH(A2,CAS2_)))

将返回TRUE或FALSE。但是，必须在按住CTRL-SHIFT键的同时按ENTER键来输入此公式。如果操作正确，Excel将在公式栏中可以看到的公式周围放置大括号{…}。

搜索函数返回一个结果数组，结果可以是#值！错误或数字。

为了返回地址，可以在CAS1中与单元格相邻的位置输入以下数组公式：

=IFERROR(ADDRESS(LARGE(ISNUMBER(SEARCH($A2,CAS2_))*ROW(CAS2_),COLUMNS($A:A)),1),"")

向右填充尽可能多的地址，然后选择组并向下填充。

在这种情况下，返回的数组是行号（即行号）的0或1*的字符串。我假设CAS2中的数据在A列中，但如果需要，您可以更改列号（如果需要，甚至可以通过将ADDRESS函数中的1替换为COLUMN（CAS2_）来计算它）

CAS1_和CAS2_要么是命名范围，要么是对两个文本组的绝对范围引用。

Answer 2

这个问题可以通过VBA来解决（至少，我认为VBA解决方案比可能的Excel解决方案要容易得多）。您需要一个宏，对于CAS1中的每一行，搜索CAS2中每一行的内容并返回您地址。

For Each cell In Sheets("CAS1").Range("A1:A" & Sheets("CAS1").Range("A1").End(xlDown).Row) '<-- check each cell of the range A1:A? of sheet CAS1 (adapt "A" and "1" if they're different)
    recFound = 0 '<-- count how many findings there are
    For Each cell2 In Sheets("CAS2").Range("A1:A" & Sheets("CAS2").Range("A1").End(xlDown).Row) '<-- check in each cell of the range A1:A? of sheet CAS2 (adapt "A" and "1" if they're different)
        If InStr(cell2.Value, cell.Value) <> 0 Then '<-- if the value in cell is contained in the value in cell2..
            recFound = recFound + 1 '<-- account the new finding
            cell.Offset(0, recFound) = Split(cell2.Address, "$")(1) & Split(cell2.Address, "$")(2) '<--write the address on the right of the currently searched cell
        End If
    Next cell2
Next cell

以上所有内容都应包含在宏中，例如，运行子makeMySearch（），以获得结果。正如我的代码中所评论的，我假设数据在A1:A ；但是它们当然可能是，例如，在B5:B 和C7:C 的第2页。您需要清楚地使代码适应您当前的数据。