忽略重复，并在Excel中创建新的唯一值列表。

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows:

我有一列值，它们经常以重复的形式出现。我需要创建一个新的列，基于第一列的唯一值，如下:

Column A   Column B  
a          a
a          b
b          c
c
c

This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the sheet2!A1 style format.

这个列B实际上需要出现在一个不同的表中，在同一个工作簿中，所以我认为它需要与sheet2一起工作!A1样式格式。

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A.

我在数据/筛选菜单选项中没有任何运气，因为这似乎只在命令中起作用。我需要列B在新值进入列a时自动更新。

11 个解决方案

#1

Totero's answer is correct. The link is also very helpful.

Totero的回答是正确的。这个链接也很有帮助。

Basically the formula you need is:

基本上你需要的公式是:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Then press ctrl+shift+enter (or it will not work using a array formula).

然后按ctrl+shift+enter(或者它不会使用数组公式)。

Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted in cell B2 (Not B1 as that will give you circular reference). Secondly this is an array formula, so you need to press ctrl+shift+enter or it will not work correctly.

这里有两件重要的事情要记住:完整的列表在单元格A2:A20，那么这个公式必须在单元B2中粘贴(不是B1，这样会给你循环引用)。第二，这是一个数组公式，所以你需要按ctrl+shift+enter或者它不能正确工作。

#2

There is a good guide of how to do this here.

这里有一个很好的指导方法。

Basically Something similar to:

基本上类似于:

=INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0))

#3

In my case the excel was frozen when using the formula of

在我的例子中，excel在使用公式时被冻结了。

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

B2 =指数(美元2:20美元,美元匹配(0,条件统计(B 1美元:B1,$ $ 2:$ 20美元),0))

because there was many rows (10000). So I did in another way which I show below.

因为有很多行(10000)。我用另一种方式做了下面的展示。

I have copied my original list to a second column and then with the function of Excel "remove duplicates" I could find the list of unique values.

我将我的原始列表复制到第二列，然后用Excel的功能“删除重复”，我可以找到唯一的值列表。

Copied from Microsoft Office Website:

从微软办公网站复制:

Select all the rows, including the column headers, in the list 
you want to filter.

你想过滤。

Click the top left cell of the range, and then drag to the bottom right cell.

点击范围的左上角单元格，然后拖到右下角的单元格。
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
The filtered list is displayed and the duplicate rows are hidden.

显示过滤后的列表，并隐藏重复的行。
On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.

将显示剪贴板任务窗格。
Make sure the filtered list is still selected, and then click Copy Copy button.
The filtered list is highlighted with bounding outlines and the selection appears as an > > item at the top of the Clipboard.

过滤后的列表用边界轮廓突出显示，选择在剪贴板顶部显示为> >项。
On the Data menu, point to Filter, and then click Show All.
The original list is re-displayed.

重新显示原始列表。
Press the DELETE key.
The original list is deleted.

原始列表被删除。
In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.

过滤后的列表出现在与原始列表相同的位置。

Source: Microsoft Office Website (link removed, cause dead)

来源:微软办公网站(链接删除，导致死亡)

#4

To remove duplicates from a column

从列中删除重复项。

Sort the values in column A A->Z
将列中的值排序为A->Z。
Select column B
选择列B
While column B is still selected, in the formula input box, enter

当B列仍然被选中时，在公式输入框中，输入。
```
=IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
```
While Column B is still selected, select Edit -> Fill -> Down (in newer versions, simply select cell B1 and pull down the outer box to expand all the way down in the column)

当B列仍然被选中时，选择Edit ->填充->(在较新的版本中，只需选择单元B1并向下拉出外框，在列中向下扩展)

Note: if column B is on another sheet, you may do Sheet1!A1 and Sheet1!A2.

注:如果B列在另一张表上，你可以做Sheet1!A1和Sheet1 ! A2。

#5

On a sorted column, you can also try this idea:

在已排序的列中，您也可以尝试以下方法:

B2=A2
B3=IFERROR(INDEX(A:A,MATCH(B2,A:A,1)+1),"")

B3 can be pasted down. It will result 0, after the last unique match. If this is unwanted, put some IF statement around to exclude this.

B3可以粘贴下来。它将在最后一个唯一匹配之后，结果为0。如果这是不需要的，就把一些If语句排除在外。

Edit:

编辑:

Easier than an IF statement, at least for text-values:

比IF语句更容易，至少对于文本值:

B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),"")

#6

In the worksheet module for the sheet containing the list:

在工作表模块中，包含列表:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngDest As Range

    If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

        Set rngDest = ThisWorkbook.Sheets("Sheet2").Range("A1")

        Me.Range(Me.Range("A2"), Me.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter _
            Action:=xlFilterCopy, CopyToRange:=rngDest, Unique:=True

    End If

End Sub

#7

I have a list of color names in range A2:A8, in column B I want to extract a distinct list of color names.

我有一列颜色名称的列表在A2:A8，在列B中，我想要提取一个不同的颜色名称列表。

Follow the below given steps:

按照下面的步骤:

Select the Cell B2; write the formula to retrieve the unique values from a list.
选择细胞B2;写出从列表中检索唯一值的公式。
=IF(COUNTIF(A$2:A2,A2)=1,A2,””)
=如果(条件统计(美元2:A2,A2)= 1,A2," ")
Press Enter on your keyboard.
按下键盘输入。
The function will return the name of the first color.
函数将返回第一个颜色的名称。
To return the value for the rest of cells, copy the same formula down. To copy formula in range B3:B8, copy the formula in cell B2 by pressing the key CTRL+C on your keyboard and paste in the range B3:B8 by pressing the key CTRL+V.
要返回其他单元格的值，请复制相同的公式。在B3:B8中复制公式:B8，通过按下键盘上的键CTRL+C，将公式复制到单元B2中，并在B3:B8键上粘贴，按CTRL+V键。
Here you can see the output where we have the unique list of color names.
在这里，您可以看到我们有唯一的颜色名称列表的输出。

#8

So for this task First Sort your data in order from A to Z or Z to A then you can just use one simple formula as stated below:

因此，对于这个任务，首先将你的数据排序从A到Z或者Z到A然后你可以用一个简单的公式如下所示:

=IF(A2=A3, "Duplicate", "Not Duplicate")

The above formula states that if column A2 data ( A is column and 2 is row number) is similar to A3 (A is Column and 3 is Row number) then it will print Duplicate else will print Not Duplicate.

上面的公式表明，如果列A2数据(A是列，2是行号)与A3 (A是列，3是行号)相似，那么它就会打印复制的else，而不是复制。

Lets consider an example, Column A consists Email address in which some are duplicate, so in Column 2, I used the above stated formula which in results displayed me the 2 duplicates cells one is Row 2 and Row 6.

让我们考虑一个例子，列A包含了电子邮件地址，其中一些是复制的，所以在第2列中，我使用了上面所述的公式，结果显示了2个重复的单元格1是第2行和第6行。

One you got the duplicate data just put filter on your sheet and make visible only the duplicate data and delete all the unnecessary data.

你得到的重复数据只是把过滤器放在你的纸上，只显示重复的数据和删除所有不必要的数据。

#9

Honestly I followed these examples to a tee and they simply didn't work. What I ended up doing after struggling pointlessly trying to get Excel to work was to just copy the entire contents of my column to NotePad++ where I was able to find an easy solution within minutes. Take a look at this: Removing duplicate rows in Notepad++

老实说，我把这些例子告诉了一个tee，它们根本就不起作用。我在毫无意义地试图让Excel工作完成后做的事情就是把我的专栏的全部内容复制到NotePad++中，这样我就能在几分钟内找到一个简单的解决方案。看一下这个:在Notepad++中删除重复的行。

Edit: Here is a brief overview of how to do it in TextFX:

这里有一个关于如何在TextFX上做的简要概述:

Plugins -> Plugin Manager -> Show Plugin Manager -> Available tab -> TextFX -> Install

插件->插件管理器->显示插件管理->可用的标签-> TextFX ->安装。

After TextFX is installed in NotePad++, then you select all your text you want to remove duplicates from, then make sure to check: TextFX -> TextFX Tools -> Sort outputs only UNIQUE lines

TextFX安装在NotePad++之后，然后选择要删除的所有文本，然后确保检查:TextFX -> TextFX Tools ->排序只输出唯一的行。

Then click "sort lines case sensitive" or "sort lines case insensitive" and it will perform the unique sort.

然后点击“sort lines区分大小写”或“sort lines case不敏感”，它将执行独特的排序。

#10

Find here mentioned above formula with error control

发现上面提到的公式与误差控制。

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

where: (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located)

其中:(B2:B9是您想要提取唯一值的列数据，D1是您的公式所在的上述单元)

#11

-6

The MODERN approach is to consider cases where column of information come from a web service such as an OData source. If you need to generate a filter select fields off of massive data that has replicated values for the column, consider the code below:

现代的方法是考虑来自web服务(例如o数据源)的信息列。如果需要从大量数据中生成过滤器选择字段，这些数据为该列复制了值，请考虑下面的代码:

var CatalogURL = getweb(currenturl)
                 +"/_api/web/lists/getbytitle('Site%20Inventory%20and%20Assets')/items?$select=Expense_x0020_Type&$orderby=Expense_x0020_Type";

/* the column that is replicated, is ordered by <column_name> */

    OData.read(CatalogURL,
        function(data,request){

            var myhtml ="";
            var myValue ="";

            for(var i = 0; i < data.results.length; i++)
            {
                myValue = data.results[i].Expense_x0020_Type;

                if(i == 0)
                {
                        myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";
                }
                else
                if(myValue != data.results[i-1].Expense_x0020_Type)
                {
                        myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";

                }
                else
                {

                }


            }

            $("#mySelect1").append(myhtml);

        });

#1