Excel:在列C中的列B中找不到列A中的值 [英] Excel: Print Values from Column A not found in Column B in Column C

问题描述

我尝试在网络上找到此解决方案，但未针对此特定问题取得成功.在Excel 2010中，我在A列中有一些数据，其中每个值都可能部分包含B列中的数据.

I have tried finding this solution on the web but have not had success for this specific problem. In Excel 2010 I have some data in column A where each value may partially contain data in column B.

EX:
列A可能包含"http://google.com/webmasters"
B列可能包含"google.com"
这应该给我一根火柴.

EX:
Column A might contain "http://google.com/webmasters"
Column B might contain "google.com"
This should give me a match.

我想在C列中打印A列中的所有值，其中不包含B列中的任何值.

I want to print in Column C all values in column A that do not contain any values from column B.

EX:
列

http://dir.mydomain.tdl
http://myotherdomain.tdl
http://blog.otherdomain.tdl
http://www.lastdomain.tdl

B列

mydomain.tdl
lastdomain.tdl

C列(需要结果)

http://myotherdomain.tdl
http://blog.otherdomain.tdl

任何帮助将不胜感激.

推荐答案

我认为我有使用 ARRAY 公式的解决方案.假设您输入的内容是，并且A-C列具有标题，或者只是列出了从单元格A2和B2开始的字符串，请执行以下操作:

I think I have the solution using ARRAY formula. Assuming your input AND that columns A-C have titles, or simply, strings are listed starting cells A2 and B2, do the following:

1. C2:输入公式=IF(OR(NOT(ISERROR(SEARCH(INDIRECT("B2:B"&(COUNTA($B:$B))),$A2)))),"",$A2)，但按 CTRL + SHIFT + ENTER ，而不是通常的 ENTER -这将定义一个ARRAY公式，并在其周围引起{}括号(但请不要手动输入！).
2. 在C2中自动填充公式，直到 A栏中的列表末尾，例如如果最后一个值在A100中，则自动填充到C100(B列的长度在这里无关紧要).
3. 然后您可以复制&将获得的结果粘贴为值，并整理出空字符串.

1. C2: type the formula =IF(OR(NOT(ISERROR(SEARCH(INDIRECT("B2:B"&(COUNTA($B:$B))),$A2)))),"",$A2) but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
2. Autofill formula in C2 until the end of list in column A, e.g. if the last value is in A100, then autofill up to C100 (how long column B does not matter here).
3. You may then copy & paste obtained results as values and sort out empty strings.

您在这里！这里的关键-我们检查A列中的每个字符串在B列中的字符串数组中是否至少有一个匹配项，并在发现至少一个匹配项的情况下返回空字符串.

Here you go! The key here - we check every string in column A for having at least one match among array of strings in column B, and return empty string in case at least one match found.

为方便起见，共享了示例文件: https://www.dropbox.com/s/janf0xxon4z2yh5/DomainsLookup.xlsx

For your convenience sample file is shared: https://www.dropbox.com/s/janf0xxon4z2yh5/DomainsLookup.xlsx

这篇关于Excel:在列C中的列B中找不到列A中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！