比较长度不相等的列以进行匹配和区别 [英] Compare columns of unequal length for matches and differences

问题描述

我将用excel术语进行解释，这样可能会更清楚。

I will explain this in excel terms just so it will probably be clearer.

我有一个带有2列的excel工作表。

I have an excel sheet with 2 columns.

列A有69,000行。
B列有49,000行。

Column A has 69,000 rows. Column B has 49,000 rows.

A列具有完整的产品列表
B列具有制造商1的产品列表

Column A has our complete product list Column B has product list from Manufacturer 1

在2列之间只有某些/某些行是公用的。而且，B列不是A列的子集。A列具有额外的条目，B列也有其他条目。

There are only certain/some rows which are common between 2 columns. and also, column B is not a subset of column A. Column A has extra entries and so does column B.

我需要知道，B列中​​的哪些行， A列
常见，B列中的行与A列不常见

I need to know, which rows from Column B, are common with Column A which rows from Column B are not common with Column A

我将如何实现？我正在尝试excel，但是vlookup会永久占用并挂断。还有其他可以帮助我的Windows /办公实用程序吗？如果它是一个宏，可以给我脚本和执行它的建议吗？

How would I acheive this? I am trying excel but the vlookup is taking forever and hanging up. Are there any other windows/office utilities which can help me? If its a macro, can you please give me scripts and suggestions to execute it?

我也可以使用linux机器，并且我对那些工具很熟悉。

I have access to linux machine aswell and I am familiar with those tools.

我可以将此信息传输到一个文本文件，我可以运行一些sed或awk脚本来打印输出吗？

I can transfer this info to a text file/s, can I run some sed or awk script to print the output?

任何帮助都会很棒。

推荐答案

使用 MATCH（）函数，它将为您提供

Use the MATCH() function, it'll give you a number if there is a result, and #NA if there isn't.

我总是在Excel 2007及更高版本的表中工作，但会给出两种语法：

I always work in Tables in Excel 2007 and newer, but will give both syntaxes:

假设您有一个表，在 column1和 column2列中进行比较，请检查Column1中是否存在Column2中的值

Assuming you have a table, with things to compare in columns "column1" and "column2", checking whether the value in Column2 is present in Column1

=ISNUMBER(MATCH(Table1[[#This Row],[Column2]],[Column1],0))

或者如果您有一个旧式数组，其中数据在A和B列中，请在A中的B中查找值：

Or if you have an old school array with data in columns A and B, looking for the value in B in A:

=ISNUMBER(MATCH(Sheet1!$B2,Sheet1!$A$2:$A$11,0))

发生了什么-您正在寻找与第一行中当前行值完全匹配（0参数）的信息，在另一列中，检查是否获得了数值（是，是否有匹配项）或没有（没有匹配项）

What's going on - you are looking for an exact match (the 0 parameter), of the value on the current row in one column, in the other column and checking whether you get a numeric value (yes there is a match), or not (no match)

这篇关于比较长度不相等的列以进行匹配和区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！