比较Excel中的两列与排除 [英] Comparing two columns in Excel with exclusion

问题描述

我想比较Excel中两列中的值，如下图所示： -

I want to compare values in two columns in Excel as depicted in the image below :-

使用公式，我想将值放在B中不存在的A的值和B的值不存在于A。任何帮助都不胜感激。

Using the formula, I want to put the values in the "Values of A which don't exist in B" and "Values of B which don't exist in A". Any help is appreciated.

我已经分享了相同的Excel表格这里。

I have shared the same excel sheet here.

推荐答案

以下内容将适用于每个，在第2行中添加公式，然后向下拖动

The following will work - for each, add the formula in row 2 and then drag down

B中不存在的A值

=IF(ISERROR(MATCH($A$2:$A$20,$B$2:$B$17,0)),A2,"")

结果= x，y，z，i，j，k，l，u

Result = x, y, z, i, j ,k, l, u

注意：您的示例电子表格不正确，因为您在Col A而不是Col B，但您不将其列在Col C的结果集中

NB: Your example spreadsheet is incorrect as u is in Col A but not Col B but you do not list it in your result set in Col C

A中不存在的值

=IF(ISERROR(MATCH($B$2:$B$17,$A$2:$A$20,0)),B2,"")

结果= q，r，e，f，g

Result = q, r, e, f, g

这篇关于比较Excel中的两列与排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！