Filter for unique value

[Christi Witte]

I have 2 sets of data that should be identical but I know they are not - how
can I filter so that it gives me only the unique values? I have found ways
of filtering to delete duplicates but that doesn't tell me which cells are
unique to that set of data.
Thanks if anyone can help

 

[eagleyzw]

you can use 'vlookup'

 

[Pete_UK]

As long as you have some column within the data which is a unique
reference (like an employee number, or stock code) then you can use
the MATCH function to see if the item in a particular record is
contained anywhere in the other data set - the formula is copied down
and would return something like "not present" if there is no match. A
similar formula can be used in the other data set to see if those
records are contained within the first data set. Then it is just a
matter of applying autofilter to the data sets to check for "not
present" to determine the unique values.

Hope this helps.

Pete

 

[Christi Witte]

I have thousands of records in each column. I would like it to compare the 2
columns and highlight a value that is not in the other column.

 

[Spiky]

I have thousands of records in each column. I would like it to compare the 2
columns and highlight a value that is not in the other column.

You could Google "morefunc" for a free set of UDFs. One of them is
supposed to pull unique values, although I haven't used it so not sure
if it fits with what you are doing. Other useful functions in
morefunc, too.

You could also use OR with arrays and conditional formatting to give
an answer. If column A and B are your data, then....
C1: =OR(A1=$B$1:$B$100)
D1: =OR(B1=$A$1:$A$100)

Enter as array with CTRL-SHIFT-ENTER, not just ENTER. Copy those
formulas down as far as necessary and adjust for proper size. Then do
conditional formatting with whatever you want to highlight the cells
on columns A and B with a formula like:
=C1=FALSE