在两列中标识具有相同值的行

I have a data set of roughly 405,000 rows and 23 columns. I need the records where the value in column "D" is the same as the value in column "H" for that row.

我有大约405,000行和23列的数据集。我需要记录，其中第D列中的值与第H列中的值相同。

So for

因此,对于

A  B  C  D  E  F  G  H 
13 8 21 ok  3  S  - of
51 7 22 no  3  A  k no
24 3 23 by  3  S  * we
24 4 24 we  3  S  ! ok
24 9 25 by  3  S  @ we
75 2 26 ok  3  S  9 ok

etc...

等等……

I'd get back the 2nd row, the 6th row, etc...

我会得到第二行，第6行，等等…

A  B  C  D  E  F  G  H 
51 7 22 no  3  A  k no
75 2 26 ok  3  S  9 ok

Based on other posts like: Formula to find matching row value based on cells in multiple columns I tried using a Pivot Table, but it complains I can't put either of my two columns in the "Columns" area because there is too much data. With both columns in the "Rows" area, I get a relationship of D to H, but I can't then find a way to filter on only those where D = H.

基于其他文章，如:公式，根据多个列中的单元格查找匹配的行值。在“行”区域的两列中，我得到了D到H的关系，但是我不能找到一种方法只对D = H的地方进行筛选。

I've also looked into countifs(), vlookup, and index / match functions, but I can't figure this out. Help please.

我还研究了countifs()、vlookup和index / match函数，但我无法理解。请帮助。

1 个解决方案

#1

I would do a simple "IF()" formula in a new column.

我将在一个新的列中做一个简单的“IF()”公式。

For your example add a new column I and use the following formula in the first data row (I2):

对于您的示例，添加一个新列I并在第一个数据行(I2)中使用以下公式:

=IF(D2=H2,"Yes","No")

Fill down to the end of the data.

在数据的末尾填写。

Then using Excel filters or countif you can check the number of "Yes" vs "No" in your data.

然后使用Excel过滤器或countif检查数据中的“是”和“不是”的数量。

#1