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

问题描述

我在Excel中有一个2列数据，如下所示: 第一列是一个值，第二列包含一个对应的单词.但是，我想删除此数据集中的行，以便最后，对于第二列中的每个唯一单词，仅保留一行，其中第一列中的值对该单词而言是最大的，甚至删除那些具有每个唯一字的最大值重复，而每个唯一字留一行.

I have a 2-column data in Excel that looks like this: The first column is a value, and the Second column contains a corresponding word. However, I want to remove rows in this dataset such that, in the end, for each unique word in Column two, only one row is retained for which the value in the Column one is the largest for that word, removing even those rows having duplicates of the largest value for each unique word and leaving one row for each unique word.

2   cat
2   cat
1   cat
3   dog
2   dog
1   dog
2   milk
1   milk
2   juice
1   juice

这些列首先按列2排序(因此单词按字母顺序:AZ)，其次按列1排序(从最大到最小，因此，最大值始终是每个单词的第一个值).

The columns are ordered firstly by Column 2 (therefore the words are in alphabetical order: A-Z) and secondly by Column 1 (from largest to smallest, therefore the largest value is always the first value for each word).

我一直在尝试使用Mac 2011的Excel中的advanced filtering来执行此操作，但是我似乎无法包括删除所有重复条目的条件，但第1列中具有最大值的条目除外.

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 largest value in Column 1.

所需的输出如下:

2   cat
3   dog
2   milk
2   juice

我一直在根据查询conditionally removing duplicates，此处搜索答案a>和此处，但是提供的每个解决方案似乎都是基于布尔条件(即是否存在信息)，并且不确定哪个值最大以作为是否要删除重复项的条件.

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 largest as the condition for which to remove the duplicate or not.

关于如何解决这个问题的任何想法?

Any ideas on how to go about solving this?

推荐答案

如果您不想使用VBA，可以尝试以下操作:

If you don not want to use VBA you can try this:

选择表格，然后从HOME -> Sort & Filter中选择Custom Sort.

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

首先用word用order : A to Z排序，然后用Add Level排序，然后用value用order: Smallest to Largest排序.

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

使用以下公式创建列Key(我假设您的数据具有标题，并从单元格A1开始.然后在单元格C2中放入公式=IF(B2<>B3,0,1)并向下复制.

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.

然后将特殊复制并粘贴为值列C并在列Key Descending上排序或在1上进行过滤

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

最后，复制已过滤的表并将其粘贴到其他位置，然后全部选择，然后按Data -> Remove Duplicates完成.或者，您可以先完成最后一部分，然后再执行上述步骤.

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屋！