# How can I single out unique values in Excel

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.

-
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. en.wikipedia.org/wiki/Exclusive_or / en.wikipedia.org/wiki/Symmetric_difference – wilson Dec 9 '10 at 11:04

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) )
``````
-
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.

-
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.

=CONCATENATE(LEFT(A1,4),RIGHT(B1,3))

Hope that helps.

-