The UNIQUE function extracts unique values from arrays or ranges automatically. Perfect for removing duplicates, generating distinct lists, data deduplication, and creating clean datasets without manual filtering.
Master the fundamentals of Excel UNIQUE function
UNIQUE is a dynamic array function that extracts distinct values from arrays automatically. It maintains the first occurrence of each unique value while removing subsequent duplicates, making it essential for data cleaning and deduplication tasks.
Removes duplicates while keeping first occurrence
Output adjusts to number of unique values found
Can compare rows or columns for uniqueness
Can return values appearing exactly once
Function-specific parameters
Function-specific return type
Clean datasets by removing duplicates
Create distinct lists from datasets
Extract unique values for reports
Identify unique categories and items
Exact matching required
Returns numeric position
Handles missing text gracefully
=UNIQUE(array, by_col, exactly_once)The array or range to extract unique values from (required)
FALSE to compare by rows (default), TRUE to compare by columns
FALSE for all unique values (default), TRUE for values appearing exactly once
Array of unique values
Description: Extracts and returns unique values from an array
Extract unique values from list
Removes duplicates, keeping first occurrence of each unique value
Create UNIQUE-like functionality in VBA
' Get unique values from range
Function UniqueVBA(inputRange As Range) As Variant
Dim dict As Object
Dim cell As Range
Dim result() As Variant
Dim i As Long
Set dict = CreateObject("Scripting.Dictionary")
' Collect unique values
For Each cell In inputRange
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, cell.Value
End If
Next cell
' Convert to array
ReDim result(1 To dict.Count, 1 To 1)
i = 1
For Each key In dict.Keys
result(i, 1) = key
i = i + 1
Next
UniqueVBA = result
End Function
' Usage example
Sub Example_UniqueVBA()
Dim uniqueValues As Variant
uniqueValues = UniqueVBA(Range("A1:A100"))
Range("C1").Resize(UBound(uniqueValues, 1), 1).Value = uniqueValues
MsgBox "Unique values: " & UBound(uniqueValues, 1)
End SubGet values appearing exactly once
' Get values appearing exactly once
Function UniqueOnceVBA(inputRange As Range) As Variant
Dim dict As Object
Dim cell As Range
Dim result() As Variant
Dim i As Long
Set dict = CreateObject("Scripting.Dictionary")
' Count occurrences
For Each cell In inputRange
If dict.exists(cell.Value) Then
dict(cell.Value) = dict(cell.Value) + 1
Else
dict.Add cell.Value, 1
End If
Next cell
' Collect values appearing exactly once
ReDim result(1 To 0, 1 To 1)
i = 0
For Each key In dict.Keys
If dict(key) = 1 Then
ReDim Preserve result(1 To UBound(result, 1) + 1, 1 To 1)
i = i + 1
result(i, 1) = key
End If
Next
UniqueOnceVBA = result
End Function
' Usage
Sub Example_UniqueOnce()
Dim onceValues As Variant
onceValues = UniqueOnceVBA(Range("A1:A200"))
If UBound(onceValues, 1) > 0 Then
Range("D1").Resize(UBound(onceValues, 1), 1).Value = onceValues
MsgBox "Values appearing once: " & UBound(onceValues, 1)
Else
MsgBox "No values appear exactly once"
End If
End SubExtract unique customer names
Get distinct product categories
Identify unique events or transactions
Extract distinct responses
Find values appearing exactly once
Not enough space for unique results
Ensure adequate space for unique valuesSolution: Clear cells to allow array to spill
by_col or exactly_once behavior unclear
Review by_col and exactly_once parametersSolution: Check parameter settings for intended behavior
Text case differences create separate unique values
=UNIQUE(UPPER(A1:A10))Solution: Use UPPER or LOWER before UNIQUE if needed
Leading/trailing spaces affect uniqueness
=UNIQUE(TRIM(A1:A10))Solution: Use TRIM before UNIQUE to normalize