Excel: Pair things up between two columns (A and B), and show unpaired values in columns C and D

Excel buffs:

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:

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

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

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')

Column D will display M

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.

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

Thanks in advance

Edit: 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


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.

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


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.

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.

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


Need Your Help

Tool for reinforcing XML, XSD and WSDL Naming convention

wsdl naming-conventions

In Java we have a wonderful tool named CheckStyle that reinforce all our corporate naming conventions. Wonderful tool. I would like to do the same with our XSD and WSDL.

Multiple layers in ggplot2 with different datasets

r ggplot2

I have a contour plot and I would like to add a geom_path with a different set of data over it.