How can I single out unique values in Excel

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.