只使用公式，在Excel中获得独特的值。

Do you know a way in Excel to "calculate" by formula a list of unique values ?

你知道Excel中的一种方法，用公式来“计算”一个唯一值的列表吗?

E.g: a range contains values "red", "blue", "red", "green", "blue", "black"
and I want to have as result "red, "blue", "green", "black" + eventually 2 other blank cells.

E。一个范围包含“红色”、“蓝色”、“红色”、“绿色”、“蓝色”、“黑色”和“我想要”的结果“红色”、“蓝色”、“绿色”、“黑色”和“最后两个空白单元格”。

I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I'd like to have this calculated sort as well, WITHOUT USING VBA.

我已经找到了一种方法，可以使用小的或大的组合索引来得到一个计算过的排序列表，但是我也想要这样的计算排序，而不使用VBA。

20 个解决方案

#1

This is an oldie, and there are a few solutions out there, but I came up with a shorter and simpler formula than any other I encountered, and it might be useful to anyone passing by.

这是一个oldie，这里有一些解决方案，但是我想出了一个比我遇到的任何一个更短更简单的公式，它可能对任何经过的人都有用。

I have named the colors list Colors (A2:A7), and the array formula put in cell C2 is this (fixed):

我已经命名了颜色列表的颜色(A2:A7)，并且在细胞C2中放入的数组公式是这个(固定的):

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

Use Ctrl+Shift+Enter to enter the formula in C2, and copy C2 down to C3:C7.

使用Ctrl+Shift+Enter键进入C2，将C2复制到C3:C7。

Explanation with sample data {"red"; "blue"; "red"; "green"; "blue"; "black"}:

用样本数据“红色”解释;“蓝色”;“红色”;“绿色”;“蓝色”;“黑色”}:

1. COUNTIF(Colors,"<"&Colors) returns an array (#1) with the count of values that are smaller then each item in the data {4;1;4;3;1;0} (black=0 items smaller, blue=1 item, red=4 items). This can be translated to a sort value for each item.
2. COUNTIF(颜色，“<”和颜色)返回一个数组(#1)，该数组的值小于数据{4;1;4;3;1;0}(黑色=0项，蓝色=1项，红色=4项)。这可以转换为每个项目的排序值。
3. COUNTIF(C$1:C...,Colors) returns an array (#2) with 1 for each data item that is already in the sorted result. In C2 it returns {0;0;0;0;0;0} and in C3 {0;0;0;0;0;1} because "black" is first in the sort and last in the data. In C4 {0;1;0;0;1;1} it indicates "black" and all the occurrences of "blue" are already present.
4. COUNTIF(C$1:C…，color)返回一个数组(#2)，其中每个数据项都已经在排序结果中。在C2中，它返回{0;0;0;0;0}和C3{0;0;0;0; 1}，因为“黑色”在排序中是第一个，在数据中是最后一个。在C4{0;1;0;0;1;1}表示“黑色”，所有出现的“蓝色”都已经出现。
5. The SUM returns the k-th sort value, by counting all the smaller values occurrences that are already present (sum of array #2).
6. SUM通过计算已经存在的所有较小值(数组#2的和)来返回k-th排序值。
7. MATCH finds the first index of the k-th sort value (index in array #1).
8. MATCH找到第k个排序值的第一个索引(数组#1中的索引)。
9. The IFERROR is only to hide the #N/A error in the bottom cells, when the sorted unique list is complete.
10. IFERROR只是在底部单元格中隐藏#N/一个错误，当排序的惟一列表完成时。

To know how many unique items you have you can use this regular formula:

要知道你有多少个独特的项目你可以使用这个规则公式:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

#2

Ok, I have two ideas for you. Hopefully one of them will get you where you need to go. Note that the first one ignores the request to do this as a formula since that solution is not pretty. I figured I make sure the easy way really wouldn't work for you ;^).

好的，我有两个主意。希望他们中的一个能帮你找到你要去的地方。注意，第一个忽略了这样做的请求，因为这个解决方案不漂亮。我想我真的确定好走的路不会为你工作,^)。

Use the Advanced Filter command

1. Select the list (or put your selection anywhere inside the list and click ok if the dialog comes up complaining that Excel does not know if your list contains headers or not)
2. 选择列表(或将您的选择放入列表中的任何位置，并单击ok，如果弹出的对话框出现了抱怨，即Excel不知道您的列表是否包含标题)
3. Choose Data/Advanced Filter
4. 选择数据/高级过滤器
5. Choose either "Filter the list, in-place" or "Copy to another location"
6. 选择“过滤列表，就地”或“复制到另一个位置”
7. Click "Unique records only"
8. 点击“独特只记录”
9. Click ok
10. 单击ok
11. You are done. A unique list is created either in place or at a new location. Note that you can record this action to create a one line VBA script to do this which could then possible be generalized to work in other situations for you (e.g. without the manual steps listed above).
12. 你是做。一个惟一的列表是在位置上创建的，也可以在新的位置创建。注意，您可以将此操作记录下来，以创建一行VBA脚本，这样就可以在其他情况下将其推广到其他情况(例如，没有上面列出的手动步骤)。

Using Formulas (note that I'm building on Locksfree solution to end up with a list with no holes)

This solution will work with the following caveats:

这个解决方案将适用以下几点说明:

The list must be sorted (ascending or descending does not matter). Actually that's quite accurate as the requirement is really that all like items must be contiguous but sorting is the easiest way to reach that state.

列表必须排序(升序或降序无关紧要)。实际上，这是非常准确的，因为要求是所有的东西都必须是连续的，但是排序是到达那个状态的最简单的方法。

Three new columns are required (two new columns for calculations and one new column for the new list). The second and third columns could be combined but I'll leave that as an exercise to the reader.

Here is the summary of the solution:

下面是解决方案的概要:

1. For each item in the list, calculate the number of duplicates above it.
2. 对于列表中的每一项，计算上面的重复数。
3. For each place in the unique list, calculate the index of the next unique item.
4. 对于唯一列表中的每个位置，计算下一个惟一项的索引。
5. Finally, use the indexes to create a new list with only unique items.
6. 最后，使用索引创建一个只有惟一项的新列表。

And here is a step by step example:

这是一步一步的例子:

1. Open a new spreadsheet
2. 打开一个新表格
3. In a1:a6 enter the example given in the original question ("red", "blue", "red", "green", "blue", "black")
4. a1:a6输入原问题的例子(“红”、“蓝”、“红”、“绿”、“蓝”、“黑”)
5. Sort the list: put the selection in the list and choose the sort command.
6. 排序列表:将选择放在列表中并选择Sort命令。
7. In column B, calculate the duplicates:
   1. In B1, enter "=IF(COUNTIF($A$1:A1,A1) = 1,0,COUNTIF(A1:$A$6,A1))". Note that the "$" in the cell references are very important as it will make the next step (populating the rest of the column) much easier. The "$" indicates an absolute reference so that when the cell content is copy/pasted the reference will not update (as opposed to a relative reference which will update).
   2. 在B1,输入“=如果(条件统计($ 1美元:A1,A1)= 1,0,条件统计(A1:$ 6美元,A1))”。注意，单元引用中的“$”非常重要，因为它将使下一步(填充该列的其余部分)变得更加容易。“$”表示绝对引用，以便在复制/粘贴该引用时，引用不会更新(相对于将更新的相对引用)。
   3. Use smart copy to populate the rest of column B: Select B1. Move your mouse over the black square in the lower right hand corner of the selection. Click and drag down to the bottom of the list (B6). When you release, the formula will be copied into B2:B6 with the relative references updated.
   4. 使用智能复制填充列B的其余部分:选择B1。把你的鼠标移动到选区右下角的黑色方块上。点击并拖动到列表的底部(B6)。当你发布的时候，这个公式会被复制到B2:B6和相关的引用更新。
   5. The value of B1:B6 should now be "0,0,1,0,0,1". Notice that the "1" entries indicate duplicates.
   6. B1的值:B6现在应该是“0,0,1,0,0,1”。请注意，“1”条目表示重复。
8. 在B列中，计算重复项:在B1中，输入=IF($1:A1,A1) = 1,0,COUNTIF(A1:$ 6,A1))。注意，单元引用中的“$”非常重要，因为它将使下一步(填充该列的其余部分)变得更加容易。“$”表示绝对引用，以便在复制/粘贴该引用时，引用不会更新(相对于将更新的相对引用)。使用智能复制填充列B的其余部分:选择B1。把你的鼠标移动到选区右下角的黑色方块上。点击并拖动到列表的底部(B6)。当你发布的时候，这个公式会被复制到B2:B6和相关的引用更新。B1的值:B6现在应该是“0,0,1,0,0,1”。请注意，“1”条目表示重复。
9. In Column C, create an index of unique items:
   1. In C1, enter "=Row()". You really just want C1 = 1 but using Row() means this solution will work even if the list does not start in row 1.
   2. 在C1,输入“=行()”。您确实需要C1 = 1，但是使用Row()表示这个解决方案将会工作，即使列表没有从第1行开始。
   3. In C2, enter "=IF(C1+1<=ROW($B$6), C1+1+INDEX($B$1:$B$6,C1+1),C1+1)". The "if" is being used to stop a #REF from being produced when the index reaches the end of the list.
   4. 在C2,输入“=如果(C1 + 1 < =行(B 6美元),C1 + 1 +指数(B美元1:B 6美元,C1 + 1),C1 + 1)”。当索引到达列表末尾时，“if”被用来阻止#REF产生。
   5. Use smart copy to populate C3:C6.
   6. 使用智能副本填充C3:C6。
   7. The value of C1:C6 should be "1,2,4,5,7,8"
   8. C1的值:C6应该是“1 2 4 5 7 8”
10. 在C列中，创建惟一项的索引:在C1中，输入“=Row()”。您确实需要C1 = 1，但是使用Row()表示这个解决方案将会工作，即使列表没有从第1行开始。在C2,输入“=如果(C1 + 1 < =行(B 6美元),C1 + 1 +指数(B美元1:B 6美元,C1 + 1),C1 + 1)”。当索引到达列表末尾时，“if”被用来阻止#REF产生。使用智能副本填充C3:C6。C1的值:C6应该是“1 2 4 5 7 8”
11. In column D, create the new unique list:
    1. In D1, enter "=IF(C1<=ROW($A$6), INDEX($A$1:$A$6,C1), "")". And, the "if" is being used to stop the #REF case when the index goes beyond the end of the list.
    2. 在D1,输入“=如果(C1 < =行(合6美元)美元指数(1美元:6美元,美元C1),”“)”。并且，当索引超出列表末尾时，“if”被用来阻止#REF案例。
    3. Use smart copy to populate D2:D6.
    4. 使用智能副本填充D2:D6。
    5. The values of D1:D6 should now be "black","blue","green","red","","".
    6. D1的值:D6现在应该是“黑色”、“蓝色”、“绿色”、“红色”、“”、“”。
12. 在D列中，创建新的惟一列表:在D1中，输入“=IF(C1<=ROW($ 6)， INDEX($ 1:$ 6,C1)”)。并且，当索引超出列表末尾时，“if”被用来阻止#REF案例。使用智能副本填充D2:D6。D1的值:D6现在应该是“黑色”、“蓝色”、“绿色”、“红色”、“”、“”。

Hope this helps....

希望这有助于....

#3

Solution

I created a function in VBA for you, so you can do this now in an easy way.
Create a VBA code module (macro) as you can see in this tutorial.

我在VBA中为你创建了一个函数，所以你现在可以用简单的方法来做。创建一个VBA代码模块(宏)，您可以在本教程中看到。

1. Press Alt+F11
2. 按Alt +季
3. Click to Module in Insert.
4. 点击插入模块。
5. Paste code.
6. 粘贴代码。
7. If Excel says that your file format is not macro friendly than save it as Excel Macro-Enabled in Save As.
8. 如果Excel说您的文件格式不是宏，而是将其保存为Excel宏，可以保存为。

Source code

Function listUnique(rng As Range) As Variant
    Dim row As Range
    Dim elements() As String
    Dim elementSize As Integer
    Dim newElement As Boolean
    Dim i As Integer
    Dim distance As Integer
    Dim result As String

    elementSize = 0
    newElement = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newElement = True
            For i = 1 To elementSize Step 1
                If elements(i - 1) = row.Value Then
                    newElement = False
                End If
            Next i
            If newElement Then
                elementSize = elementSize + 1
                ReDim Preserve elements(elementSize - 1)
                elements(elementSize - 1) = row.Value
            End If
        End If
    Next

    distance = Range(Application.Caller.Address).row - rng.row

    If distance < elementSize Then
        result = elements(distance)
        listUnique = result
    Else
        listUnique = ""
    End If
End Function

Usage

Just enter =listUnique(range) to a cell. The only parameter is range that is an ordinary Excel range. For example: A$1:A$28 or H$8:H$30.

输入=listUnique(范围)到单元格。唯一的参数是一个普通的Excel范围。例如:$1:$28或$8:$30。

Conditions

• The range must be a column.
• 范围必须是列。
• The first cell where you call the function must be in the same row where the range starts.
• 调用该函数的第一个单元格必须位于范围开始的同一行。

Example

Regular case

1. Enter data and call function.
   
2. 输入数据和调用函数。
3. Grow it.
   
4. 它生长。
5. Voilà.
   
6. 瞧。

Empty cell case

It works in columns that have empty cells in them. Also the function outputs nothing (not errors) if you overwind the cells (calling the function) into places where should be no output, as I did it in the previous example's "2. Grow it" part.

它在含有空单元的列中工作。另外，如果将单元(调用函数)覆盖到不应该输出的位置(就像我在前面的示例中所做的那样)，函数将不会输出任何(不是错误)。”部分。

#4

A roundabout way is to load your Excel spreadsheet into a Google spreadsheet, use Google's UNIQUE(range) function - which does exactly what you want - and then save the Google spreadsheet back to Excel format.

一个roundabout的方法是将你的Excel电子表格加载到谷歌的电子表格中，使用谷歌的唯一的(range)函数——你想做什么就做什么——然后将谷歌电子表格保存到Excel格式。

I admit this isn't a viable solution for Excel users, but this approach is useful for anyone who wants the functionality and is able to use a Google spreadsheet.

我承认，对于Excel用户来说，这不是一个可行的解决方案，但是这种方法对于任何想要使用该功能并且能够使用谷歌电子表格的人来说都是有用的。

#5

noticed its a very old question but people seem still having trouble using a formula for extracting unique items. here's a solution that returns the values them selfs.

注意到这是一个非常古老的问题，但是人们似乎仍然难以使用一个公式来提取唯一的项目。这里有一个返回值的解决方案。

Lets say you have "red", "blue", "red", "green", "blue", "black" in column A2:A7

假设你有“红色”，“蓝色”，“红色”，“绿色”，“蓝色”，“黑色”在列A2:A7。

then put this in B2 as an array formula and copy down =IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")

然后把这个放在B2中作为一个数组公式，然后抄下来=IFERROR(INDEX(A$2:A$7; IF(频率匹配($2:$2:$7;$2:$7;);ROW(间接的($2:$2:$7));ROW(间接的)($2:$2:$2:$7));

then it should look something like this;

它应该是这样的;

#6

Try this formula in B2 cell

在B2单元中试试这个公式。

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")

After click F2 and press Ctrl + Shift + Enter

点击F2，按Ctrl + Shift + Enter。

#7

You could use COUNTIF to get the number of occurence of the value in the range . So if the value is in A3, the range is A1:A6, then in the next column use a IF(EXACT(COUNTIF(A3:$A$6, A3),1), A3, ""). For the A4, it would be IF(EXACT(COUNTIF(A4:$A$6, A3),1), A4, "")

您可以使用COUNTIF来获取范围内值的发生次数。因此，如果值在A3中，范围是A1:A6，那么在下一列中使用if(确切的(COUNTIF(A3:$ 6, A3)，1)， A3，“”)。对于A4来说，如果(确切的说(A4:$ 6, A3)，1)， A4，“”

This would give you a column where all unique values are without any duplicate

这将为您提供一个列，其中所有惟一值都是没有副本的。

#8

Assuming Column A contains the values you want to find single unique instance of, and has a Heading row I used the following formula. If you wanted it to scale with an unpredictable number of rows, you could replace A772 (where my data ended) with =ADDRESS(COUNTA(A:A),1).

假设列A包含您想要找到的唯一的唯一实例，并且有一个标题行，我使用了下面的公式。如果您希望它以不可预测的行数进行扩展，那么可以用=ADDRESS(COUNTA(A:A)，1)替换A772(我的数据结束的地方)。

=IF(COUNTIF(A5:$A$772,A5)=1,A5,"")

=如果(条件统计(A5:$ 772澳元,A5)= 1,A5," ")

This will display the unique value at the LAST instance of each value in the column and doesn't assume any sorting. It takes advantage of the lack of absolutes to essentially have a decreasing "sliding window" of data to count. When the countif in the reduced window is equal to 1, then that row is the last instance of that value in the column.

这将在列中每个值的最后一个实例中显示惟一的值，并且不承担任何排序。它利用了缺乏绝对的优势来减少数据的“滑动窗口”。当reduce窗口中的countif等于1时，该行是该列中该值的最后一个实例。

#9

Even to get a sorted unique value, it can be done using formula. This is an option you can use:

即使要得到一个排序的唯一值，也可以使用公式来完成。这是一个你可以使用的选项:

=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))

range data: A2:A18

数据范围:A2:那么

formula in cell C2

在细胞C2公式

This is an ARRAY FORMULA

这是一个数组公式。

#10

Drew Sherman's solution is very good, but the list must be contiguous (he suggests manually sorting, and that is not acceptable for me). Guitarthrower's solution is kinda slow if the number of items is large and don't respects the order of the original list: it outputs a sorted list regardless.

Drew Sherman的解决方案很好，但是列表必须是连续的(他建议手动排序，这对我来说是不可接受的)。如果条目的数量很大，并且不尊重原始列表的顺序，那么Guitarthrower的解决方案会比较慢:它会输出一个已排序的列表。

I wanted the original order of the items (that were sorted by the date in another column), and additionally I wanted to exclude an item from the final list not only if it was duplicated, but also for a variety of other reasons.

我想要项目的原始顺序(按另一列的日期排序)，另外，我想要将一个项目从最终列表中排除掉，不仅是它被复制了，而且还有其他各种原因。

My solution is an improvement on Drew Sherman's solution. Likewise, this solution uses 2 columns for intermediate calculations:

我的解决方案是改进Drew Sherman的解决方案。同样，该解决方案使用2列进行中间计算:

Column A:

列:

The list with duplicates and maybe blanks that you want to filter. I will position it in the A11:A1100 interval as an example, because I had trouble moving the Drew Sherman's solution to situations where it didn't start in the first line.

这个列表有重复的内容，可能还有你想要过滤的空白。我将把它定位在A11:A1100区间作为一个例子，因为我很难将Drew Sherman的解决方案移到第一行没有开始的情况下。

Column B:

列B:

This formula will output 0 if the value in this line is valid (contains a non-duplicated value). Note that you can add any other exclusion conditions that you want in the first IF, or as yet another outer IF.

如果这一行的值有效(包含一个非重复值)，则该公式将输出0。注意，您可以在第一个IF中添加任何其他的排除条件，或者作为另一个外部条件。

=IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))

Use smart copy to populate the column.

使用智能副本填充列。

Column C:

列C:

In the first line we will find the first valid line:

在第一行，我们会找到第一条有效线:

=MATCH(0;B11:B1100;0)

From that position, we search for the next valid value with the following formula:

从这个位置，我们用下面的公式搜索下一个有效值:

=C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)

Put it in the second line and use smart copy to fill the rest of the column. This formula will output #N/D error when there is no more unique itens to point. We will take advantage of this in the next column.

把它放在第二行，用smart copy来填充该列的其余部分。这个公式将输出#N/D错误，当没有更独特的信息时。我们将在下一篇专栏文章中利用这一点。

Column D:

列D:

Now we just have to get the values pointed by column C:

现在我们只需要得到C列的值:

=IFERROR(INDEX($A$11:$A$1100; C11); "")

Use smart copy to populate the column. This is the output unique list.

使用智能副本填充列。这是输出唯一列表。

#11

You can also do it this way.

你也可以这样做。

Create the following named ranges:

创建以下命名范围:

nList = the list of original values
nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1
nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")

With these 3 named ranges you can generate the ordered list of unique values with the formula below. It will be sorted in ascending order.

使用这3个命名范围，您可以使用下面的公式生成惟一值的有序列表。它将按升序排序。

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")

You will need to substitute the row number of the cell just above the first element of your unique ordered list for the '?' character.

您需要将单元格的行号替换为“?”的唯一有序列表的第一个元素之上?'字符。

eg. If your unique ordered list begins in cell B5 then the formula will be:

如。如果你的唯一有序列表从B5开始，那么公式将是:

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")

#12

I'm surprised this solution hasn't come up yet. I think it's one of the easiest

我很惊讶这个解决方案还没有出现。我认为这是最简单的方法之一。

Give your data a heading and put it into a dynamic named range (i.e. if your data is in col A)

给你的数据一个标题，并把它放入一个动态命名的范围内(也就是说，如果你的数据在col a)

=OFFSET($A$2,0,0,COUNTA($A:$A),1)

And then create a pivot table, making the source your named range.

然后创建一个透视表，使源文件成为你命名的范围。

Simply putting the heading into the rows section and you'll have the unique values, sort any way you like with the inbuilt feature.

简单地将标题放入行部分，您将拥有惟一的值，按照您喜欢的内置特性排序。

#13

I've pasted what I use in my excel file below. This picks up unique values from range L11:L300 and populate them from in column V, V11 onwards. In this case I have this formula in v11 and drag it down to get all the unique values.

我在下面的excel文件中粘贴了我所使用的文件。这将从范围L11中获取惟一值:L300，并从列V (V11)开始填充它们。在这种情况下，我在v11中有这个公式，并把它拖下来得到所有的唯一值。

=INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))

or

或

=INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))

this is an array formula

这是一个数组公式。

#14

Resorting to a PivotTable might not count as using formulas only but seems more practical that most other suggestions so far:

求助于数据透视表可能并不仅仅是使用公式，但似乎更实用，到目前为止，大多数其他建议:

#15

I ran into the same problem recently and finally figured it out.

我最近遇到了同样的问题，终于弄明白了。

Using your list, here is a paste from my Excel with the formula.

使用你的列表，这是我的Excel表格的粘贴。

I recommend writing the formula somewhere in the middle of the list, like, for example, in cell C6 of my example and then copying it and pasting it up and down your column, the formula should adjust automatically without you needing to retype it.

我建议将公式写在列表中间的某个地方，比如，例如，在我的示例的cell C6中，然后复制并粘贴到你的列上，公式应该自动调整，而不需要重新输入。

The only cell that has a uniquely different formula is in the first row.

唯一有唯一不同公式的单元格在第一行。

Using your list ("red", "blue", "red", "green", "blue", "black"); here is the result: (I don't have a high enough level to post an image so hope this txt version makes sense)

使用您的列表(“红”、“蓝”、“红”、“绿”、“蓝”、“黑色”);这是结果:(我没有足够高的级别来发布图像，所以希望这个txt版本有意义)

• [Column A: Original List]
• (列原始列表):
• [Column B: Unique List Result]
• [B栏:唯一的列表结果]

• [Column C: Unique List Formula]

  [C栏:唯一的列表公式]

  1. red, red, =A3
  2. 红色,红色,= A3
  3. blue, blue, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
  4. 蓝色,蓝色,=如果(返回错误(匹配(A4,3美元:A3,0)),A4," ")
  5. red, , =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
  6. 红色,,=如果(返回错误(匹配(A5,3美元:A4,0)),A5," ")
  7. green, green, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
  8. 绿色,绿色,=如果(返回错误(匹配(A6,美元3:A5,0)),A6," ")
  9. blue, , =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
  10. 蓝色,,=如果(返回错误(匹配(A7,3美元:A6,0)),A7," ")
  11. black, black, =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")
  12. 黑色,黑色,=如果(返回错误(匹配(A8,3美元:A7,0)),A8," ")

#16

This only works if the values are in order i.e all the "red" are together and all the "blue" are together etc. assume that your data is in column A starting in A2 - (Don't start from row 1) In the B2 type in 1 In b3 type =if(A2 = A3, B2,B2+1) Drag down the formula until the end of your data All " Red" will be 1 , all "blue" will be 2 all "green" will be 3 etc.

这只有在值为i时才有效。e“红色”在一起,所有的“蓝色”等假设您的数据列从A2 -(不从第一行开始)B2输入1 b3 type =如果(A2 = A3、B2、B2 + 1)拖累公式直到最后数据的所有“红色”将是1,所有“蓝色”将2 3等“绿色”。

In C2 type in 1, 2 ,3 etc going down the column In D2 = OFFSET($A$1,MATCH(c2,$B$2:$B$x,0),0) - where x is the last cell Drag down, only the unique values will appear. -- put in some error checking

在C2类型中，1、2、3等在D2 =偏移($ 1 $1,MATCH(C2，$B$2:$B$x,0))中，x是最后一个单元拖放，只有唯一的值会出现。——进行一些错误检查。

#17

For a solution that works for values in multiple rows and columns, I found the following formula very useful, from http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Oscar at get-digital.help.com even goes through it step-by-step and with a visualized example.

对于一个为多个行和列的值工作的解决方案，我发现下面的公式非常有用，从http://www.get- digitalhelp.com/2009/03/16/- value -from- multicolumns-usingarray-formula / Oscar at get-digital.help.com甚至可以一步一步地进行，并使用一个可视化的例子。

1) Give the range of values the label tbl_text

1)给出标签tbl_text的取值范围。

2) Apply the following array formula with CTRL + SHIFT + ENTER, to cell B13 in this case. Change $B$12:B12 to refer to the cell above the cell you enter this formula into.

2)用CTRL + SHIFT + ENTER，将下面的数组公式应用到cell B13中。改变B$12:B12是指细胞上方的细胞，你输入这个公式。

    =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

3) Copy/drag down until you get N/A's.

3)复制/拖放，直到你得到N/A。

#18

If one puts all the data in the same columns and uses the following formula Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

如果将所有数据放入相同的列，并使用以下公式示例公式:= If (C105=C104，“Duplicate”，“Not a Duplicate”)

Steps

步骤

1. Sort the data
2. 对数据排序
3. Add column for the formula
4. 为公式添加列。
5. Checks if the cell equals the cell above it
6. 检查单元格是否等于上面的单元格。
7. Then filter Not a Duplicate
8. 然后过滤，而不是复制。
9. Optional: Copy the data calculated by the formula column and paste as values only (that way if you start deleting data, you don't start to get errors
10. 可选:复制公式列计算的数据，并将其粘贴为值(如果开始删除数据，就不会出现错误。
11. NOTE/WARNING: This only works if you sort the data first
12. 注意/警告:如果您先对数据进行排序，那么这只会起作用。

Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

示例公式:=IF(C105=C104，“Duplicate”，“Not a Duplicate”)

#19

Optimized VBScript Solution

I used totymedli's code but found it bogging down when using large ranges (as pointed out by others), so I optimized his code a bit. If anyone is interested in getting unique values using VBScript but finds totymedli's code slow when updating, try this:

我使用了totymedli的代码，但是在使用大范围时(如其他人所指出的)，我发现它会崩溃，所以我对他的代码进行了一些优化。如果有人想使用VBScript获取惟一的值，但是在更新时发现totymedli的代码很慢，请尝试以下方法:

    Function listUnique(rng As Range) As Variant
        Dim val As String
        Dim elements() As String
        Dim elementSize As Integer
        Dim newElement As Boolean
        Dim i As Integer
        Dim distance As Integer
        Dim allocationChunk As Integer
        Dim uniqueSize As Integer
        Dim r As Long
        Dim lLastRow  As Long

        lLastRow = rng.End(xlDown).row

        elementSize = 1
        unqueSize = 0

        distance = Range(Application.Caller.Address).row - rng.row

        If distance <> 0 Then
            If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then
                listUnique = ""
                Exit Function
            End If
        End If

        For r = 1 To lLastRow
            val = rng.Cells(r)
            If val <> "" Then
                newElement = True
                For i = 1 To elementSize - 1 Step 1
                    If elements(i - 1) = val Then
                        newElement = False
                        Exit For
                    End If
                Next i
                If newElement Then
                    uniqueSize = uniqueSize + 1
                    If uniqueSize >= elementSize Then
                        elementSize = elementSize * 2
                        ReDim Preserve elements(elementSize - 1)
                    End If
                    elements(uniqueSize - 1) = val
                End If
            End If
        Next


        If distance < uniqueSize Then
            listUnique = elements(distance)
        Else
            listUnique = ""
        End If
    End Function

#20

Select the column with duplicate values then go to Data Tab, Then Data Tools select remove duplicate select 1) "Continue with the current selection" 2) Click on Remove duplicate.... button 3) Click "Select All" button 4) Click OK

选择具有重复值的列，然后转到Data选项卡，然后数据工具选择删除重复的Select 1)“继续当前选择”2)点击删除复制按钮3)点击“选择所有”按钮4)点击确定。

now you get the unique value list.

现在你得到了唯一的值列表。