Excel：在两列（A和B）之间进行匹配，并在列C和D列中显示不成对的值 [英] Excel: Pair things up between two columns (A and B), and show unpaired values in columns C and D

问题描述

Excel buff：

Excel buffs:

在搜索两列之间的比较（即：使用VLOOKUP）时，显示的结果很多，但是我没有看到结果似乎以这种特定的方式做我需要的：

There are many results showing up when searching for comparisons between two columns (ie: using VLOOKUP) but none of the results I have looked so far seems to do what I need in this particular way:

列A具有以下值：Z，Q，V，V，T，T

Column A has following values: Z, Q, V, V, T, T

列B具有以下值：V，T，T，M

Column B has following values: V, T, T, M

列C将显示Z，Q，V（这里我们有一个V，因为一组V对，留给我们一个不配对的V）

Column C will display Z, Q, V (here we have one V because one set of 'V' pairs up, leaving us with one unpaired 'V')

列D将显示M

我以前看过的其他示例假定列C不会有V，因为它已经在列A中找到，无论它出现了多少次。

The other examples I've seen so far assumes Column C will not have 'V' in it because it's already found in Column A, regardless of how many times it showed up.

基本上，我需要两列配对和删除之间的值，但让我有任何奇怪的出来。

Basically, instead, I need values between two columns paired up and removed, but leave me with any "odd ones" out.

我已经无法使用公式了解这一个 - 我已经先将所有内容排序，然后将列A或B中的单元格向下移动直到列A和B有匹配的值或奇怪的一个在每行

I've been unable to figure this one out using formulae - I've resorted to sorting everything first, then shifting cells in either Column A or B downwards until Columna A and B either have matching values or odd one out in each row

提前感谢

编辑：另一种方式说：我想删除列A和B中的配对值，直到所有对已被删除，留下我在列A和B中剩余的值

Another way of saying it: I'd like to "eliminate" paired up values from Columns A and B, until all pairs have been removed, leaving me with remaining values in Column A and B

推荐答案

我们假设C / D中的答案被允许与A / B中与无与伦比的原件居住在同一行。

Let's make the assumption that the answers in C/D are allowed to reside in the same row as the unmatched originals in A/B.

这是C1的公式，将其复制并粘贴：

Here's the formula for C1, copy and paste it down:

=REPT(A1,
 MAX(0,MIN(1,COUNTIF($A:$A,A1)
 -COUNTIF($B:$B,A1)
 -IF(ROW()=1,0,COUNTIF(OFFSET($C$1,0,0,ROW()-1,1),A1))
 )))

基本上，我们要在c中重复相应的值olumn A 如果我们没有在B< em< / em>中找到匹配项，我们至今还没有在C中考虑它。

Basically, we want to "repeat" the corresponding value in column A if we haven't found a match for it in B and we haven't already accounted for it in C so far.

这里有逻辑来确保OFFSET（）不引用零高度范围，并且我们在C的每一行重复0或1次，不再更少，也不少。

There's logic in there to ensure that OFFSET() doesn't refer to a zero-height range, and that we repeat either 0 or 1 time, no more and no less, on each row of C.

D1的公式是相似的，但反向比较B返回到A：

The formula for D1 is similar, but reversed to compare B back to A:

=REPT(B1,
 MAX(0,MIN(1,COUNTIF($B:$B,B1)
 -COUNTIF($A:$A,B1)
 -IF(ROW()=1,0,COUNTIF(OFFSET($D$1,0,0,ROW()-1,1),B1))
 )))

这篇关于Excel：在两列（A和B）之间进行匹配，并在列C和D列中显示不成对的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！