It's easy to achieve by using
array formula[
^]. How to:
Create an array formula - Office Support[
^].
I'd suggest to move unique data into - let's say -
Sheet2
- column
A
. Then in
Sheet1
(with non-unique data) - column
B
add the following formula (assuming that a first row is a header row):
= SUM(IF(A2=Sheet2!A$2:A$901,1,0))
Accept it by pressing combination of keys:
Ctrl+Shift+Enter
Fill-down above formula till the end of data in
Sheet1
.
This will return the number of occurencies:
0
- none (new - unique),
>=1
- exits (duplicated)
Finally, add filter to column B (higher than zero) and remove duplicated data ;)
Good luck!