Create a Unique List in Excel Based on Criteria (9 Methods)

Method 1 – Create a List of Unique Rows with Multiple Column Criteria

In the following dataset, we have a list of players, their ages, and what sport they participate in. The table on the right is the output table where we’ll extract data based on the unique values only. For example, we want to know the names of the unique participants only along with their ages.

Create a List of Unique Rows with Multiple Columns Criteria

The generic formula of the UNIQUE function is:

=UNIQUE(array, [by_col], [exactly_once])

The UNIQUE function is only available in Microsoft 365.

  • Insert the following function in cell F5:
=UNIQUE(B5:C13,FALSE,FALSE)
  • Press Enter, and the function returns a spill range into a column.

Create a List of Unique Rows with Multiple Columns Criteria

Read More: How to Generate List Based on Criteria in Excel


Method 2 – Get a List of Unique Values Sorted in Alphabetical Order

  • In the output cell E5, the required formula will be:
=SORT(UNIQUE(B5:C13,FALSE,FALSE),,1)
  • After pressing Enter, the function will return the array sorted in alphabetical order by A to Z.

Get a List of Unique Values Sorted in Alphabetical Order


Method 3 – Make a List of Unique Values Concatenated into One Cell

Let’s extract the values for each player into a single cell.

  • The required formula in the output Cell F5 will be:
=UNIQUE(B5:B13&", "&C5:C13)
  • The formula will return an array of unique rows in a single column.

Make List of Unique Values Concatenated into One Cell

Read More: How to Make a Comma Separated List in Excel


Method 4 – Create a List of Unique Values with Criteria (UNIQUE-FILTER Formula)

Case 1 – Identify Unique Values Based on Multiple AND Criteria in Excel

We’ll add a few criteria and extract unique data based on those conditions. For example, we want to know the unique names of the participants who have participated in swimming only and are below 25 years old.

Create a List of Unique Values with Criteria

  • The formula for the output cell F5 will be:
=UNIQUE(FILTER(B5:C13,(D5:D13=G9)*(C5:C13<G10)))

Create a List of Unique Values with Criteria

  • After pressing Enter, the formula will return a spill array with the unique names and the corresponding ages based on the selected criteria.

Create a List of Unique Values with Criteria


Case 2 – Search Unique Values Based on Multiple OR Criteria

Let’s search for unique participants who took part in swimming, cycling, or both. These sports are listed in a separate column.

  • The required formula in cell F5 will be now:
=UNIQUE(FILTER(B5:C13,(D5:D13=F11)+(D5:D13=F12)))

Create a List of Unique Values with Criteria

  • Press Enter, and you’ll get the extracted unique data in a spill range.

Create a List of Unique Values with Criteria


Case 3 – Get a List of Unique Values Ignoring Blank Cells

Let’s input a few blank values into the dataset. We’ll now force the formula to ignore those.

  • The required formula in the output cell F5 will be:
=UNIQUE(FILTER(B5:C13,D5:D13<>""))

Create a List of Unique Values with Criteria

  • Press Enter to apply the formula, and it will return an array of non-blank unique values in a spill range.

Create a List of Unique Values with Criteria

Read More: How to Make a To Do List in Excel


Method 5 – Find a List of Unique Values in Specified Columns in Excel

From our data table, we’re going to extract the names of the unique participants and show the outputs from the Name (Column B) and Sports Name (Column D) columns only.

  • The required formula in Cell F5 will be:
=UNIQUE(CHOOSE({1,3},B5:B13,C5:C13,D5:D13))

Find a List of Unique Values in Specified Columns in Excel

  • Apply the formula to get the results.

Find a List of Unique Values in Specified Columns in Excel

Inside the CHOOSE function, the index numbers are 1 and 3 which implies that the 1st and 3rd arrays of cells have to be selected from the list of values. The UNIQUE function then considers the specified columns only and returns the unique data from those columns in an array.

Read More: How to Make a Numbered List in Excel


Method 6 – Use the IFERROR Function While Creating a Unique List

Let’s say we’re checking for the names of unique participants who are under 21. Since there aren’t any, the formula should return a #N/A error. Let’s instead display a customized message “Not Found”.

  • Insert the following formula in Cell F5:
=IFERROR(UNIQUE(FILTER(B5:C13,C5:C13<=G10)), "Not Found")

Use of IFERROR Function While Creating a Unique List

  • The formula will return the specified message as shown in the following screenshot if it doesn’t find any results.

Use of IFERROR Function While Creating a Unique List


Method 7 – Extract a Unique List Based on Criteria (INDEX-MATCH Formula)

You can apply this method in other versions of Excel.

Steps:

  • Select the output Cell F5 and insert the following formula:
=INDEX(B5:B13, MATCH(0, IF($F$12=$D$5:$D$13,COUNTIF($F$4:$F4, $B$5:$B$13), ""), 0))
  • Press Enter.

Extract a Unique List Based on Criteria (INDEX-MATCH Formula)

  • Use the Fill Handle to drag the cell to the right.
  • This will fetch the age of the participant.

Extract a Unique List Based on Criteria (INDEX-MATCH Formula)

  • From Cell G5, fill down the column until a #N/A value appears.

Extract a Unique List Based on Criteria (INDEX-MATCH Formula)

How Does the Formula Work?

  • COUNTIF($F$4:$F4, $B$5:$B$13): The COUNTIF function here has been used to store and count all the cells available in the range of B5:B13. And the function returns as:

{0;0;0;0;0;0;0;0;0}

  • IF($F$12=$D$5:$D$13, COUNTIF($F$4:$F4, $B$5:$B$13), “”): The IF function searches for the given criteria in the cells and returns as:

{0;””;0;””;””;0;””;””;””}

  • The MATCH function returns the row number of the cell found in the previous step.
  • Finally, the INDEX function extracts data based on those row numbers.

Method 8 – Prepare a Unique List in Excel Based on Multiple Criteria

Let’s fetch the unique names of the participants who have participated in swimming and are younger than 25.

Prepare a Unique List in Excel Based on Multiple Criteria

  • The required formula in the output cell F5 will be:
=IFERROR(INDEX($B$5:$B$13,MATCH(0,COUNTIF(F4:$F$4,$B$5:$B$13)+IF(D5:D13=$G$9,1,0)+IF(C5:C13<$G$10,1,0),0)),"")

Prepare a Unique List in Excel Based on Multiple Criteria

  • Press Enter and AutoFill down the column.

Prepare a Unique List in Excel Based on Multiple Criteria

In this formula, we have assigned the criteria with two IF functions and the COUNTIF function will consider those criteria while extracting the array of output cells. As described in the previous method, the INDEX-MATCH formula will return the output based on that array. And the IFERROR function here has been used to return a customized message if any error is found.


Method 9 – Make Multiple Unique Lists Along Rows and Columns with Criteria

We’ve modified the dataset slightly. The table at the bottom will show the unique names of the participants per sport.

Make Multiple Unique Lists Along Rows and Columns with Criteria

  • We’ve named the range of cells (B5:C13) as Sports.
  • The columns have headers with the names- SportsName and Name. You must keep in mind that the headers in the table cannot occupy any space.
  • Insert the following formula in B16:
=IFERROR(INDEX(Sports,SMALL(IF(Sports[SportsName]=B$15,ROW(Sports)-4),ROW(1:1)),2),"")

Make Multiple Unique Lists Along Rows and Columns with Criteria

  • Press Enter.
  • Use the Fill Handle to fill down the column until a blank cell appears.

Make Multiple Unique Lists Along Rows and Columns with Criteria

  • Copy the range of cells (B16:B20) in the first column.
  • In Cell C16, paste the values with the Formulas (F) option.
  • AutoFill down until you get a blank cell.

Make Multiple Unique Lists Along Rows and Columns with Criteria

  • Repeat for cells D16 and E16.

And you’ll get the other unique names of the participants based on the sports types right away. But here you must remember that you cannot simply use Fill Handle to autofill the cells rightward from the first return values in Column B. Using the Autofill option will result in manipulated data and you won’t be able to extract the original output.

Make Multiple Unique Lists Along Rows and Columns with Criteria

How Does the Formula Work?

  • IF(Sports[SportsName]=B$15, ROW(Sports)-4): This part of the function searches for the given condition specified by the header in Cell B15 and the function returns as:

{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}

  • SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): The SMALL function extracts the smallest number from the previous output and for Cell C16, it’s ‘1’.
  • The INDEX function pulls out the name based on the row number specified by the SMALL function.
  • The IFERROR function has been used to show blank cells if any error output is found.
  • In this combined formula, the number ‘4’ in the portion- “ROW(Sports)-4” is the row number of the header in the primary data table.

Read More: How to Create List from Range in Excel


Download the Practice Workbook


Related Articles


<< Go Back to Make List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo