Select the entire column, then apply “Conditional Formatting” and click “New Rule”.
The goal is to highlight all the duplicate cells yellow.
Select format type: “Format only unique or duplicate values”
Format All: “Duplicate”
Format: Set format to highlight yellow
Sort by all the yellow highlighted cells (make sure you select “sort” NOT “Filter”)
Then copy all the yellow highlighted cells and paste them as “Values” in a blank tab.
Now you can highlight the cells you just pasted and removed duplicates (Data — Remove Duplicates)
