Getting unique values in Excel by using formulas only

Question

Do you know a way in Excel to  calculate  by formula a list of unique values     E g  a vertical 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     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

User · Answer

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.

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.

User · Answer

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

User · Answer

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  now you get the unique value list

User · Answer

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

User · Answer

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

User · Answer

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    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   Choose Data Advanced Filter  Choose either  Filter the list  in-place  or  Copy to another location   Click  Unique records only   Click ok  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     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     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 item   Finally  use the indexes to create a new list with only unique items    And here is a step by step example     Open a new spreadsheet  In a1 a6 enter the example given in the original question   red    blue    red    green    blue    black    Sort the list  put the selection in the list and choose 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 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    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   The value of B1 B6 should now be  0 0 1 0 0 1   Notice that the  1  entries indicate duplicates     In Column C  create an index of unique items    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   In C2  enter   IF C1 1 lt  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   Use smart copy to populate C3 C6   The value of C1 C6 should be  1 2 4 5 7 8    In column D  create the new unique list    In D1  enter   IF C1 lt  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   Use smart copy to populate D2 D6   The values of D1 D6 should now be  black   blue   green   red            Hope this helps

User · Answer

If one puts all the data in the same columns and uses the following formula Example Formula   IF C105 C104  Duplicate   Not a Duplicate    Steps   Sort the data Add column for the formula Checks if the cell equals the cell above it Then filter Not a Duplicate 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 NOTE WARNING   This only works if you sort the data first   Example Formula   IF C105 C104  Duplicate   Not a Duplicate

User · Answer

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    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   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   Column 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    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   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   Column D   Now we just have to get the values pointed by column C    IFERROR INDEX  A 11  A 1100  C11         Use smart copy to populate the column  This is the output unique list

User · Answer

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     IF COUNTIF A5  A 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

User · Answer

Simple formula solution  Using dynamic array functions  UNIQUE function  Since fall 2018  the subscription versions of Microsoft Excel  Office 365   Microsoft 365 app  contain so called dynamic array functions  not yet available in Office 2016 2019 nonsubscription versions   UNIQUE function One of those functions is the UNIQUE function that will deliver an array of unique values for the selected range  Example In the following example  the input values are in range A1 A6  The UNIQUE function is typed into cell C1   UNIQUE A1 A6    As you can see  the UNIQUE function will automatically spill over the necessary range of cells in order to show all unique values  This is indicated by the thin  blue frame around C1 C4  Good to know As the UNIQUE function automatically spills over the necessary number of rows  you should leave enough space under the C1  If there is not enough space  you will get a  SPILL error   If you want to reference the results of the UNIQUE function  you can just reference the cell containing the UNIQUE function and add a hash   sign   C1   It is also possible to check unique values in several columns  In this case  the UNIQUE function will deliver all rows where the combination of the cells within the row are unique   If you wish to show unique columns instead of unique rows  you have to set the  by col  argument to TRUE  default is FALSE  meaning you will receive unique rows   You can also show values that appear exactly once by setting the  exactly once  argument to TRUE   UNIQUE A1 A6  TRUE

User · Answer

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   I have named the colors list Colors  A2 A7   and the array formula put in cell C2 is this  fixed     IFERROR INDEX Colors MATCH SUM COUNTIF C 1 C1 Colors   COUNTIF Colors   lt   amp Colors  0         Use Ctrl Shift Enter to enter the formula in C2  and copy C2 down to C3 C7   Explanation with sample data   red    blue    red    green    blue    black      COUNTIF Colors   lt   amp 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  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  The SUM returns the k-th sort value  by counting all the smaller values occurrences that are already present  sum of array  2   MATCH finds the first index of the k-th sort value  index in array  1   The IFERROR is only to hide the  N A error in the bottom cells  when the sorted unique list is complete    To know how many unique items you have you can use this regular formula     SUM IF FREQUENCY COUNTIF Colors   lt   amp Colors  COUNTIF Colors   lt   amp Colors   1

User · Answer

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

User · Answer

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       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  lt  gt  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  lt  gt     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  gt   elementSize Then                         elementSize   elementSize   2                         ReDim Preserve elements elementSize - 1                      End If                     elements uniqueSize - 1    val                 End If             End If         Next           If distance  lt  uniqueSize Then             listUnique   elements distance          Else             listUnique              End If     End Function

User · Answer

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   lt    amp  A 2  A 18  0     range data  A2 A18  formula in cell C2  This is an ARRAY FORMULA

User · Answer

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   1  Give the range of values the label 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        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

User · Answer

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  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   amp COUNTA A 2 A 7     ROW INDIRECT  1   amp COUNTA A 2 A 7        ROW A1         then it should look something like this

User · Answer

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    Press Alt F11 Click to Module in Insert  Paste code  If Excel says that your file format is not macro friendly than save it as Excel Macro-Enabled in Save As    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  lt  gt     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  lt  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   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   Enter data and call function   Grow it   Voil       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

User · Answer

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       This would give you a column where all unique values are without any duplicate

User · Answer

Try this formula in B2 cell   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

User · Answer

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    lt   amp 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   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   IFERROR INDEX nList MATCH SMALL nUnique ROW  -4  nUnique 0

User · Answer

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

[excel] Getting unique values in Excel by using formulas only

Examples related to excel

Examples related to excel-formula