在两个列中找到副本并将它们提取到一个新的列中

I have two columns with different lengths contains numbers like the following:

我有两个不同长度的列包含如下数字:

column 1  column 2

1          8
2          9
20         1
4          2
5          20 
6

what I want to do is find the duplicates in the two columns and place them in a new column like the following:

我想做的是找到两个列中的重复项，并将它们放在一个新的列中，如下所示:

column 1  column 2  column 3

1          8          1
2          9          2
20         1         20
4          2
5          20 
6

so is there any way that I can get this result in excel?

有办法用excel得到这个结果吗?

Thanks in advance!

提前谢谢!

1 个解决方案

#1

This should do it.

这应该这样做。

For you with Columns A and B:

A栏和B栏:

=IF(ISERROR(MATCH(A1,$B$1:$B$5,0)),"",A1)

=如果(返回错误(匹配(A1,B 1美元:$ 5美元,0)),”“,A1)

If your B range extends beyond 5, just change $5 to whatever the last row is, so $40 if 40 rows. If you have headers in Row 1, change A1 to A2 and $B$1 to $B$2.

如果您的B范围超过5，只需将5美元更改为最后一行，如果是40行，则为40美元。如果第1行有头文件，将A1更改为A2，将$B$1更改为$B$2。

Copy the formula into C1:C6 or whatever the length of A is. (A doesn't have to be longer than B, you just need the formula to span one column, and the match target to span the other).

把公式复制到C1 C6或者A的长度。(A不需要比B长，只需要公式张成一列，匹配目标张成另一列)。

From there, you can copy the data as values to D and sort Ascending to remove the empty cells and do with that what you want.

从那里，您可以将数据复制到D中，并排序提升以删除空单元，并按照您想要的方式进行处理。

#1