vba 在两列配对的值中删除每对重复项（行）中的一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21623647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove one of each pair of duplicates (row) in values paired in two columns
提问by End.Game
I have two columns from which I need to remove duplicate rows. For this example:
我有两列需要从中删除重复的行。对于这个例子：
A b
sport 1 pippo
sport 1 pippo
sport 1 pluto
sport 2 paperino
sport 2 paperino
sport 3 gastone
my required output is:
我需要的输出是：
A b
sport 1 pippo
sport 1 pluto
sport 2 paperino
sport 3 gastone
I'm new to Excel so don't know what kind of formula or VBA to use.
我是 Excel 新手，所以不知道要使用什么样的公式或 VBA。
How might I achieve this?
我怎么能做到这一点？
采纳答案by chris neilsen
You can use Remove Duplicatesmanually, from the Data tab (you should do this to get an understanding of how it works)
您可以Remove Duplicates从“数据”选项卡手动使用（您应该这样做以了解它的工作原理）
Or, if you really want to automate it, try this
或者，如果你真的想自动化它，试试这个
Sub Demo()
Dim ws As Worksheet
Dim rng As Range
' Get a reference to the sheet your data is on
Set ws = ActiveSheet '<-- change to suit
With ws
' Get a reference to your data
Set rng = Range(.Cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp))
' Apply Remove Duplicates
rng.RemoveDuplicates Columns:=2, Header:=xlNo
End With
End Sub
回答by pnuts
There are several ways to achieve what you want, of which the simplest has to be as mentioned by @chris neilsen:
有几种方法可以实现您想要的，其中最简单的必须是@chris neilsen 提到的：
Remove Duplicates
删除重复项
Just select your two columns then Data > Data Tools - Remove Duplicates accept the defaults (probably) and click OK. As indicated in a comment however, not all versions of Excel have such functionality.
只需选择您的两列，然后数据 > 数据工具 - 删除重复项接受默认值（可能）并单击确定。然而，正如评论中所指出的，并非所有版本的 Excel 都具有此类功能。
Note the Remove- they are gone for good once the Undo stack is overwritten.
请注意删除- 一旦撤消堆栈被覆盖，它们就永远消失了。
Beware also that Remove Duplicates is not totally reliable (see link in Advanced Filter below).
另请注意，删除重复项并不完全可靠（请参阅下面高级过滤器中的链接）。
Advanced Filter
高级过滤器
I tend to prefer this as Remove Duplicates may have a defect(though extremely rare to show up!):
我倾向于这样做，因为Remove Duplicates 可能有缺陷（虽然很少出现！）：
It is mandatory (or at least highly advisable) to ensure your columns are labelled for this. Again select your two columns, then Data > Sort & Filter - Advanced, select Copy to another location, choose Copy to range (one cell is sufficient) and obviously check Unique records only.
确保您的列为此标记是强制性的（或至少是非常可取的）。再次选择您的两列，然后数据 > 排序和过滤器 - 高级，选择复制到另一个位置，选择复制到范围（一个单元格就足够了）并且显然只选中唯一记录。
Here Copy tois a give away to the fact that your entire original list (duplicates and all) is preserved, as may sometimes be required, without the bother of creating a copy to work on first.
这里复制到是一个事实，即您的整个原始列表（副本和所有）都被保留，有时可能需要，而无需先创建副本以进行处理。
COUNTIF
伯爵
A formula solution may be more appropriate when the removal of duplicates is to be taken to mean bothof a pair, etc.
当去除重复的将被认为是指式解决方案可能是更合适的两个一对等的
Something like:
就像是：
=COUNTIF(B:B,B1)
in B1 (assuming your labels are in Row1) and copied down to suit will identify pairs or other multiples. Having achieved the count then filter to delete the chosen selection.
在 B1 中（假设您的标签在 Row1 中）并向下复制以适应将识别对或其他倍数。达到计数然后过滤以删除所选的选择。
COUNTIF is generally available in all Excel versions (I don't recall whether in the very earliest ones!)
COUNTIF 通常在所有 Excel 版本中都可用（我不记得是否在最早的版本中！）
COUNTIFS
COUNTIFS
Is a function only available in the more recent versions of Excel, but allows for a more complicated definition of "Duplicate" - not applicable in your example.
是仅在较新版本的 Excel 中可用的函数，但允许对“重复”进行更复杂的定义 - 在您的示例中不适用。
PivotTable
数据透视表
PT's aggregate Row Labels values as a matter of course, so give the appearance of removing duplicates. PT's are so useful they may well be wanted for other reasons anyway, so no extra bother for removing duplicates.
PT 的聚合 Row Labels 值是理所当然的，因此看起来像是删除重复项。PT 非常有用，无论如何可能出于其他原因需要它们，因此无需额外麻烦删除重复项。
Show in Tabular Form Table layout may be most convenient (here with Aabove bin Row Labels). This should display one instance of each A/b pair - unless the A value is not the first example. In other words the display of ColumnA values does not repeat ColumnA values - just blanks that imply "same as above" until A changes. I think the most recent versions of Excel have a feature for displaying the A values on every row but it is quite easy to 'make allowances' in earlier versions.
以表格形式显示表格布局可能是最方便的（这里与A上面b的行标签）。这应该显示每个 A/b 对的一个实例 - 除非 A 值不是第一个示例。换句话说，ColumnA 值的显示不会重复 ColumnA 值 - 只是表示“与上述相同”的空白，直到 A 更改。我认为最新版本的 Excel 具有在每一行上显示 A 值的功能，但在早期版本中很容易“允许”。
The problem though is that since the contents of the PT cannot be altered in the way proposed below this needs to be on a copy of the data showing in the PT (not merely another version of the PT!)
但问题是，由于 PT 的内容不能以下面建议的方式更改，因此需要在 PT 中显示的数据副本上（不仅仅是 PT 的另一个版本！）
Select the Column with values only at the startof each 'section', Home > Editing > Find & Select - Go To Special..., Blanks. Click on one of the selected cell, enter =, Up arrow and CTRL+Enter.
选择仅在每个“部分”开头的具有值的列，主页 > 编辑 > 查找和选择 - 转到特殊...，空白。单击所选单元格之一，输入=、向上箭头和CTRL+ Enter。
VBA
VBA
This is a solution to almost anything "Excel" and viable for removing duplicates, though would probably not be 'cost effective' for a complete data sample of the size in your question - unless the process is required often.
这是几乎所有“Excel”的解决方案，并且可以删除重复项，尽管对于您问题中大小的完整数据样本来说可能不是“具有成本效益” - 除非经常需要该过程。
I have probably missed a few other options - but no doubt they have been mentioned by others before I had barely started with this screed.
我可能错过了其他一些选择——但毫无疑问，在我刚刚开始使用这个熨平板之前，其他人已经提到过它们。
