Getting unique values ​​in Excel using formulas only

Question

Do you know that Excel calculates a list of unique values ​​using a formula?

For example: the range contains the values "red" , "blue" , "red" , "green" , "blue" , "black"
and I want to get the result of "red , "blue" , "green" , "black" + in the end 2 other empty cells.

I already found a way to get the calculated sorted list using SMALL or LARGE in combination with INDEX, but I would also like to have this calculated view, WITHOUT USING VBA.

Answer 1

This is old, and there are several solutions, but I came up with a shorter and simpler formula than any other that I came across, and it can be useful to anyone who passes by.

I called the colors list Colors (A2: A7) and the array formula placed in cell C2 is ( fixed ):

 =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 to C3: C7 .

Explanation with data samples {"red"; "Blue"; "Red"; "Green"; "Blue"; "The black" }:

• COUNTIF(Colors,"<"&Colors) returns an array (# 1) with a number of values ​​less than each data element {4; one; four; 3; one; 0} (black = 0 units less, blue = 1 item, red = 4 items). This can be translated into a sort value for each item.
• 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 the first in the sorting and the last in the data. In C4 {0; one; 0; 0; one; 1} it indicates black, and all occurrences of blue are already present.
• SUM returns the sort value k-th , by counting all the smaller values ​​that are already present (sum of array # 2).
• MATCH finds the first index of the kth sort value (index in array # 1).
• IFERROR is only to hide the #N/A error in the bottom cells when the sorted unique list is complete.

To find out how many unique elements you have, you can use this correct formula :

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

Answer 2

Ok, I have two ideas for you. I hope one of them takes you where you need to go. Please note that the first ignores the request in order to do this as a formula, since this solution is not very beautiful. I decided that I am sure that a simple way really will not work for you; ^).

Use the Advanced Filter command

• Select a list (or put your choice anywhere in the list and click OK if a message appears in the dialog box that Excel does not know whether your list contains headers or not)
• Select Data / Advanced Filter
• Select "Filter the list in place" or "Copy to another location"
• Click "Only Unique Entries"
• Click ok
• You are done. A unique list is created either locally or in a new location. Please note that you can record this action to create one line of VBA script to do this, which could then be generalized to work in other situations for you (for example, without the control steps described above).

Using formulas (note that I was building a Locksfree solution to get a list without holes)

This solution will work with the following reservations:

The list should be sorted (ascending or descending does not matter). In fact, this is pretty accurate, since the requirement really is that all such objects should be adjacent, but sorting is the easiest way to achieve this state. Three new columns are required (two new columns for calculations and one new column for a new list). The second and third columns can be combined, but I will leave this as an exercise for the reader.

Here is a summary of the solution:

• For each item in the list, calculate the number of duplicates above it.
• For each place in the unique list, calculate the index of the next unique element.
• Finally, use indexes to create a new list with only unique elements.

And step by step:

• Open a new table
• In a1: a6, enter the example specified in the original question ("red", "blue", "red", "green", "blue", "black")
• Sort list: place the selection on the list and select the sort command.
• In column B, calculate the duplicates:
  • In B1, enter "= IF (COUNTIF ($ A $ 1: A1, A1) = 1.0, COUNTIF (A1: $ A $ 6, A1))." Note that the "$" in cell references is very important, as this will make the next step (filling in the rest of the column) much easier. "$" Indicates an absolute link, so when the contents of the cell are copied / pasted, the link will not be updated (as opposed to the relative link that will be updated).
  • Use a smart copy to populate the rest of column B: select B1. Hover over the black square in the lower right corner of the selection. Click and drag down to the end of the list (B6). When you release, the formula will be copied to B2: B6 with the updated relative links.
  • The value of B1: B6 should now be "0,0,1,0,0,1". Note that entries "1" indicate duplicates.
• In column C, create an index of unique elements:
  • 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 on line 1.
  • In C2, enter "= IF (C1 + 1 <= ROW ($ B $ 6), C1 + 1 + INDEX ($ B $ 1: $ B $ 6, C1 + 1), C1 + 1)." "If" is used to stop creating #REF when the index reaches the end of the list.
  • Use a smart copy to populate C3: C6.
  • C1 value: C6 should be "1,2,4,5,7,8"
• In column D, create a new unique list:
  • In D1, enter "= IF (C1 <= ROW ($ A $ 6), INDEX ($ A $ 1: $ A $ 6, C1)," ")". And "if" is used to stop the #REF case when the index goes outside the list.
  • Use a smart copy to populate D2: D6.
  • Values ​​D1: D6 should now be black, blue, green, red,,,.

Hope this helps ...

Answer 3

Decision

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

• Press Alt + F11
• Click Module in Insert .
• Paste the code.
• If Excel says that your file format is not macro-friendly, it saves it as Excel Macro-Enabled in Save As .

Source

 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 

Using

Just enter =listUnique(range) in the cell. The only parameter is range , which is a regular Excel range. For example: A$1:A$28 or H$8:H$30 .

Condition

• range should be a column.
• The first cell in which you call the function must be on the same line where range begins.

Example

Ordinary case

• Enter data and call function.
  
• Grow it.
  
• Voila.
  

Empty cell housing

It works on columns that have empty cells. Also, the function does not return anything (and not errors) if you drag cells (calling the function) to places where there should be no output, as I did in the previous example. 2. Grow it.

Answer 4

A workaround is to upload an Excel spreadsheet to a Google spreadsheet, use the Google UNIQUE (range) function, which does exactly what you want, and then save the Google spreadsheet in Excel format.

I admit that this is not a viable solution for Excel users, but this approach is useful for anyone who wants to use this functionality and can use the Google spreadsheet.

Answer 5

his very old question remarked, but people still seem to have difficulty using a formula to extract unique items. here is a solution that returns values ​​for itself.

Let's say you have in column A2: A7

there is "red", "blue", "red", "green", "blue", "black",

then put this in B2 as an array formula and copy =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)));"")

then it should look something like this;

Answer 6

Even to get a sorted unique value, this can be done using a 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)) 

: A2:A18

in cell C2

This is FORMULA MACHINES

Answer 7

Try this formula in cell B2

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

After pressing F2 and press Ctrl + Shift + Enter

Answer 8

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

This will give you a column where all unique values ​​will not contain any duplicates

Answer 9

Assuming column A contains the values ​​you want to find one unique instance and has a title bar, I used the following formula. If you want it to scale with an unpredictable number of rows, you could replace A772 (where my data ended up) with = ADDRESS (COUNTA (A: A), 1) .

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

This displays a unique value in the LAST instance of each value in the column and does not involve any sorting. It uses a lack of absolutes to essentially have a decreasing “sliding window” of data for counting. When the score in the reduced window is 1, this row is the last instance of this value in the column.

Answer 10

Drew Sherman's solution is very good, but the list should be contiguous (it offers manual sorting, and this is unacceptable to me). Guitartrower's solution is a bit slower if the number of elements is large and does not match the order of the original list: it displays a sorted list independently.

I need the initial order of the items (which were sorted by date in another column), and, in addition, I wanted to exclude the item from the final list not only if it was duplicated, but for a number of other reasons,

My solution is an improvement on the Drew Sherman solution. Similarly, this solution uses 2 columns for intermediate calculations:

Column A:

A list with duplicates and possibly spaces that you want to filter. As an example, I will put it in the interval A11: A1100, because I was not able to move Drew Sherman's solution in a situation where it did not start on the first line.

Column B:

This formula will print 0 if the value in this line is valid (contains a non-duplicate value). Note that you can add any other exception conditions you want in the first IF, or as another external IF.

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

Use a smart copy to populate the column.

Column C:

In the first line we find the first valid line:

 =MATCH(0;B11:B1100;0) 

From this position, we are looking for the following valid value with the following formula:

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

Put it on the second row and use a smart copy to fill in the rest of the column. This formula will throw a # N / D error if there is no more unique point. We will take advantage of this in the next column.

Column D:

Now we just need to get the values ​​indicated by column C:

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

Use a smart copy to populate the column. This is a unique, unique conclusion.

Answer 11

I inserted what I use in my excel file below. This selects unique values ​​from the L11:L300 range and fills them from columns V, V11 and beyond. In this case, I have this formula in v11 and drag it down to get all unique values.

 =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

Answer 12

You can also do it this way.

Create the following name 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 three named ranges, you can create an ordered list of unique values ​​using the formula below. It will be sorted in ascending order.

 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 '?' character.

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

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

Answer 13

I am surprised that this decision has not yet come. I think this is one of the easiest

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

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

And then create the pivot table by creating the original named range.

Just put the title in the line section and you will get unique values, sort them in any way using the built-in function.

Answer 14

Reducing to a pivot table may not be considered only using formulas, but it seems more practical than most other suggestions:

Answer 15

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

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

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

The only cell having a uniquely different formula is on the first line.

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

• [Column A: Original List]
• [Column B: Unique List Result]

• [Column C: unique list formula]

  • red, red, =A3
  • blue, blue, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
  • red, =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
  • green, green, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
  • blue, =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
  • black, black, =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")

Answer 16

This only works if the values ​​are in order, i.e. all “red” together, and all “blue” together, etc. Suppose your data is in column A, starting from A2 - (do not start from row 1) In B2 type in 1 In b3 type = if (A2 = A3, B2, B2 + 1) Drag the formula to the end of your data. All “red” will be 1, all “blue” will be 2, all “green” will be 3, etc.

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

Answer 17

For a solution that works for values ​​in multiple rows and columns, I found the following formula very useful: 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.

1) Give a range of values ​​to the tbl_text label

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 into which you enter this formula.

  =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 until you get N / A.

Answer 18

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

Steps

• Data sorting
• Add Column for Formula
• Checks if the cell matches the cell above it
• Then filter Not a Duplicate
• Optional: copy the data calculated by the formula column and paste only the values ​​(this way, if you start deleting the data, you will not start getting errors
• NOTE / WARNING: This only works if you are sorting data first.

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

Answer 19

Optimized VBScript Solution

I used the totymedli code, but found that it fades when using large ranges (as others have indicated), so I optimized its code a bit. If someone is interested in getting unique values ​​using VBScript, but totymedli code is slow when updating, try the following:

  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 

Answer 20

, "", " " " " 1) " " 2) .... 3) " " 4) ""

.