This post will guide you how to extract unique values in a range based on one criteria with a formula in Excel. How do I create a unique distinct list based on one condition in Excel.

1. Extract Unique List of Values based on Criteria Using Formula

Assuming that you have a list of data in range A1:C6, in which contain price data for each product. And you want to extract a unique product list from this range and return non-duplicates that are unique based on the month value. For example, you want to extract a product name list based on Jan month.  You can use an Excel array formula based on the INDEX function, the MATCH function, the IF function, and the COUNTIF function to achieve the result. Here is the formula that it will extract the unique distinct product name list based on month value:

=INDEX($B$2:$B$6, MATCH(0, IF($D$2=$A$2:$A$6, COUNTIF($E$1:$E1, $B$2:$B$6), ""), 0))

Type this formula into Cell E2, and press Ctrl + Shift + Enter keys to change it as Array formula.  The drag the AutoFill handle until you get the #N/A value.

You would notice that the unique product name list is extracted in column E.

Note: the range B2:B6 is a range that contains the unique product name list that you want to extract. A2:A6 is a range that contain criteria you want to base on.  Cell D2 contain one criteria that you want to use.

2. Extract a Unique List based on Criteria using User-Defined Function (VBA)

The below tutorial will guide you through extracting a unique list based on criteria using a user-defined function created with VBA code in Excel.

Step1: Press Alt + F11 to open the VBA Editor.

Step2: In the VBA Editor, go to the “Insert” menu and select “Module.” This will create a space to write your VBA code.

Step3: Copy and paste the VBA code we provided for the user-defined function into the module.

Function ExtractUniqueList(DataRange As Range, Criteria As Variant) As Variant
    Dim UniqueList As Object
    Set UniqueList = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 1 To DataRange.Rows.Count
        If DataRange.Cells(i, 1).Value = Criteria And Not UniqueList.Exists(DataRange.Cells(i, 2).Value) Then
            UniqueList(DataRange.Cells(i, 2).Value) = 1
        End If
    Next i
    
    Dim Output() As Variant
    ReDim Output(1 To UniqueList.Count, 1 To 1)
    
    Dim Key As Variant
    i = 1
    For Each Key In UniqueList.Keys
        Output(i, 1) = Key
        i = i + 1
    Next Key
    
    ExtractUniqueList = Output
End Function

Step4: Save and close the VBA Editor.

In a cell, enter a formula like this:

=ExtractUniqueList(A2:B6,D2)

This formula will return unique values from column B (B2:B6) based on the criteria in cell D2, where D2 should match values in column A (A2:A6).

3. Video: Extract Unique List of Values based on Criteria

In this video, we’ll explore not just one, but two methods to achieve this. The first method involves using Excel array formulas with INDEX, MATCH, IF, and COUNTIF functions. The second method employs a user-defined function created with VBA code.

4. Related Functions

• Excel COUNTIF function
  The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel INDEX function
  The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
• Excel MATCH function
  The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….