根据列中最高的值有条件地删除Excel中的重复项 [英] Conditionally removing duplicates in Excel based on highest value in a Column

问题描述

我在excel中有一个2列文件，看起来像这样：
第一列是一个值，第二列包含一个对应的单词。但是，我需要删除其中第1列中的值不是最高的所有重复项。基本上，清理这个数据表，有数百万条目。

  -2 cat 
 -2 cat 
 -1猫
 -3狗
 -2狗
 -1狗
 -2牛奶
 -1牛奶
 -2果汁
 -1 juice 
  

我想删除第2列中没有最高价值的所有重复项列1.列首先由列2排序（因此单词按字母顺序：AZ），其次是列1（从最高到最低，因此最高值始终是每个单词的第一个值）。

我一直在尝试使用Excel for Mac 2011中的高级过滤来实现，但我似乎不能包含条件删除所有重复条目，除了第1列中具有最高值的条目。

所需的输出如下：

  -2 cat 
 -3 dog 
 -2 milk 
 -2 juice 
  / pre> 
 
 
我一直都是根据查询有条件地删除副本，此处和此处，然而每个所提供的解决方案似乎是基于布尔条件（即有没有信息），并且不确定哪个值是最高的，作为删除重复的条件。 
 
 
 
任何关于如何解决这个问题的想法？
解决方案
如果您不想使用VBA，可以尝试以下方式：
 
 
 
选择表格，从 HOME  - >排序&过滤器选择自定义排序。
 
 
 
首先按 与订单：A到Z 然后添加级别并按 $ 订单：最小到最大。
 
 
 
创建列 Key 使用以下公式（我假设你的数据有标题，从单元格开始 A1 然后在单元格 C2 将公式 = IF（B2<> B3,0,1）并复制。
 
 
 
然后复制并粘贴特殊值作为值列 C 并在列键上排序 降序或过滤 1  
 
 
 
最后，复制过滤表并将其粘贴到其他位置，然后选择它，并且数据 - >删除重复和您的完成，或者您可以先完成最后一部分，然后运行上述步骤。 > 
I have a 2-column file in excel that that looks like this:
The first column is a value, and the Second column contains a corresponding word. However, I need to remove all of the duplicates for which their value in Column 1 is not the highest. Essentially, cleaning up this data sheet, that has millions of entries.
-2  cat
-2  cat
-1  cat
-3  dog
-2  dog
-1  dog
-2  milk
-1  milk
-2  juice
-1  juice
I want to remove all duplicates in Column 2 that do not have the highest value in Column 1. The columns are ordered firstly by Column 2 (therefore the words are in alphabetical order: A-Z) and secondly by Column 1 (from highest to lowest, therefore the highest value is always the first value for each word).

I have been trying to do this using the advanced filtering in Excel for Mac 2011, but I cannot seem to include the condition of removing all duplicate entries except for the entry that has the highest value in Column 1.

The desired output is as follows:
-2  cat
-3  dog
-2  milk
-2  juice
I have been searching for answers based on the query conditionally removing duplicates, here and here, however each of the solutions provided seems to be based on a boolean condition (i.e. there is information or not), and not determining which value is the highest as the condition for which to remove the duplicate or not. 

Any ideas on how to go about solving this?
解决方案
If you don not want to use VBA you can try this:

Select the table and from HOME -> Sort & Filter select Custom Sort.

First sort by word with order : A to Z then Add Level and sort by value with order: Smallest to Largest.

Create a column Key with the following formula (i am assuming you data have headers and start from cell A1. Then in cell C2 put the formula =IF(B2<>B3,0,1) and copy down.

Then copy and paste special as values column C and sort on the column Key Descending or filter on 1

Finally, copy the filtered table and paste it elsewhere then select it all and Data -> Remove Duplicates and your done. Or you can do this last part first and then run the steps above.

                        
这篇关于根据列中最高的值有条件地删除Excel中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！