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.
This question is related to
excel
excel-formula
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
Not a Duplicate
Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")
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
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
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.
Module
in Insert
.Excel Macro-Enabled
in Save As
.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
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
.
range
must be a column.range
starts.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.
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.
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 C: Unique List Formula]
=A3
=IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
=IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
=IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
=IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
=IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")
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.
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)),"")
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.
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
formula in cell C2
This is an ARRAY FORMULA
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
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.
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).
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
.
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)
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.
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.
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.
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 ;^).
This solution will work with the following caveats:
Here is the summary of the solution:
And here is a step by step example:
Hope this helps....
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:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")
then it should look something like this;
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 <> 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
Source: Stackoverflow.com