CODEWITHSUNDEEP
excelunique
Adrian
Adrian

Reputation: 11

Find unique values and remove duplicates in excel

Is there anyway to find the unique values and remove those duplicates in excel.

Example

Hello
Hello
Dear
Hi
Aloha
Hi

The result 

Dear
Aloha

I tried with the standard Remove Duplicates. But it seems like the result is a below. 

Hello
Dear
Hi
Aloha

I'm trying to find unique values, that is around 3000 records from 600k data.

Kindly advice.

Upvotes: 1

Views: 193

Answers (1)

pnuts
pnuts

Reputation: 59450

Not tested on a sample of the size required, but potentially simple and fast is to apply a formula of the kind suggested by @tospig within Conditional Formatting, format with a fill colour and then filter to select and delete that colour.

So please try: select ColumnA, HOME, Styles, Conditional Formating, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: 

=COUNTIF(A:A,A1)>1

Format..., choose a fill colour, OK, OK.

Now filter ColumnA to select that fill colour and delete all visible. If to delete 597k rows this way Excel might have a problem but the filtering might be achieved in stages of limited range each.

Upvotes: 1

Related Questions