Is there anyway to find the unique values and remove those duplicates in excel.
是否存在找到唯一值并删除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.
我在寻找唯一的值，从60万数据中得到3000条记录。
Kindly advice.
请建议。
1 个解决方案
#1
1
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.
没有对所需大小的示例进行测试，但是潜在的简单和快速的方法是在条件格式中应用@tospig建议的那种公式，使用填充颜色的格式，然后过滤以选择和删除该颜色。
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::
所以请尝试:选择ColumnA, HOME, Styles，条件格式，新规则…，使用公式确定要格式化哪些单元格，将该公式为真的值格式化:
=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.
现在过滤器ColumnA选择填充颜色和删除所有可见。如果以这种方式删除597k行，Excel可能会有问题，但是过滤可能在每个有限的范围内完成。
#1
1
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.
没有对所需大小的示例进行测试，但是潜在的简单和快速的方法是在条件格式中应用@tospig建议的那种公式，使用填充颜色的格式，然后过滤以选择和删除该颜色。
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::
所以请尝试:选择ColumnA, HOME, Styles，条件格式，新规则…，使用公式确定要格式化哪些单元格，将该公式为真的值格式化:
=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.
现在过滤器ColumnA选择填充颜色和删除所有可见。如果以这种方式删除597k行，Excel可能会有问题，但是过滤可能在每个有限的范围内完成。