Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm not sure unique is the right term but what I'm looking for is if I have column A with values 1,2,3,4,5 and column B with 3,4,5,6,7 I want the result to display 1,2,6,7.

share|improve this question
Is each value in a separate cell or do you have 1,2,3,4,5 in a single cell? Do you mean you want a 3rd column that contains just the unique values from both columns? – Rhys Gibson Dec 8 '10 at 21:45
I think you can call it Exclusive Or (XOR) or Symmetric Difference of column A & B. / – wilson Dec 9 '10 at 11:04
up vote 1 down vote accepted

I hope there's an easier way... Assuming you don't care if the values within some column are unique:

You can use the MATCH function to find some value in some range. Like to find the index (position) of the value in A1 within the range B1:B5, use:

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

The $ makes the range B1:B5 absolute, and thus fixed when copying this formula to other cells. (In Excel, one could also use B:B to search the whole column instead.) The last parameter, 0, makes this independent of any sort order in B1:B5. Likewise, to get the index of the value in B1 within the range A1:A5:

=MATCH(B1, $A$1:$A$5, 0)

Due to using 0 for the last parameter, this yields #N/A if the value is not found. This can be trapped with ISERROR, like so:

=IF( ISERROR( MATCH(A1, $B$1:$B$5, 0)), A1 & " is unique", "" )
=IF( ISERROR( MATCH(B1, $A$1:$A$5, 0)), B1 & " is unique", "" )

With the first 2 formulas in columns C and D, and the latter 2 formulas in columns E and F:

I don't really know how to combine the results from columns E and F into a nice overview. You could use the same formulas for conditional formatting though, to apply some specific formatting to the values themselves when unique. Above I did that in columns A and B, using conditions like:

ISERROR( MATCH(A1, $B$1:$B$5, 0) )
ISERROR( MATCH(B1, $A$1:$A$5, 0) )
share|improve this answer
To not return the index, but the search value itself, use =VLOOKUP(A1, B$1:B$5, 1, false) instead of MATCH. – Arjan Dec 8 '10 at 23:18
Thanks Arjan! Worked great! – Chris Dec 13 '10 at 14:16

You could make another colum with all the values in, name it appropriately (eg nums), and make a pivot table from it. Then use nums as the row label, and Count of nums in the value field, and apply a Value filter from the row labels menu to filter for values that appear only once. Then the row list will only have the numbers you are interested in.

share|improve this answer
Good idea! Thanks. – Chris Dec 13 '10 at 14:15

You can use the LEFT RIGHT or MID function with the CONCATENATE function to make this work.


Hope that helps.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .