How to create a list from two columns in excel

In Microsoft Excel, several methods are available to create a unique list under multiple criteria. The unique list is generally prepared to remove duplicates from a table. In this article, youll learn how to use different methods to generate a unique list based on several conditions.

Download Practice Workbook

You can download the Excel workbook that weve used to prepare this article.

9 Methods to Create a Unique List in Excel Based on Criteria

1. Create a List of Unique Rows with Multiple Columns Criteria

In the following picture, the table on the left is lying with several random sports names, the participants and their ages. Another table on the right is the output table where well 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.

Well use the UNIQUE function here which returns the unique values from a range or array. The generic formula of this function is:

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

Although the UNIQUE function is only available in Excel 365, well also show the alternative methods with the combinations of other conventional functions in the last 3 sections before jumping into the concluding words.

So, the required function in Cell F5 will be:

After pressing Enter, the function will return an array with the unique names along with the corresponding ages of the participants.

2. Get a List of Unique Values Sorted in Alphabetical Order

We can also get the unique values sorted in alphabetical order by using the SORT function. By denoting the sort order with ascending (A to Z) or descending (Z to A) criteria, we can rearrange the participant names based on their initials.

In the output Cell E5, the required formula with the SORT and UNIQUE functions will be:

After pressing Enter, the function will return the array sorted in alphabetical order by A to Z.

3. Make a List of Unique Values Concatenated into One Cell

Lets assume, we want to extract unique data from two columns and then join the values alongside to return concatenated values in a single column. In the following picture, the unique names and the corresponding ages will be shown together in a column by using a specific delimiter- Comma (,). And Well use Ampersand (&) here to concatenate the values alongside two different columns.

The required formula in the output Cell F5 will be:

The formula will return an array of unique rows in a single column.

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

i. Identify Unique Values Based on Multiple AND Criteria in Excel

In this section, well 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. So, we have to combine UNIQUE and FILTER functions here to filter data with the given criteria.

The necessary formula in the output Cell F5 will be:

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

ii. Search Unique Values Based on Multiple OR Criteria

Lets assume, now we want to know the names of the unique participants who took part in both outdoor sports- swimming and cycling. As were assigning the criteria from only one column, we have to simply use numerical addition to join two conditions in the FILTER function.

The required formula in Cell F5 will be now:

Press Enter and youll get the extracted unique data right away as shown in the following screenshot.

iii. Get a List of Unique Values Ignoring Blank Cells

Our dataset may contain several blank cells or rows. So, if we want to skip those blank rows and extract unique data from the table we have to use a comparison operator: Not Equal To (<>) in the formula.

The required formula in the output Cell F5 will be:

After pressing Enter, the formula will return the following array with only non-blank unique rows in the output table.

5. Find a List of Unique Values in Specified Columns in Excel

Assuming that we want to extract unique data from a number of specific columns only. Its not possible to select those distinct columns with a mouse cursor and input it as the argument for the UNIQUE function. So, we have to combine the CHOOSE function here with the UNIQUE function as the CHOOSE function will let you select any number of specified columns or the ranges of cells based on the index numbers from a list of values.

From our data table, were 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:

After pressing Enter, the formula will return the unique names and the names of the corresponding sports from two distinct columns as shown in the picture below.

Inside the CHOOSE function, the index numbers are 1 and 3 which implies that the 1st and 3rd ranges 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.

6. Use of IFERROR Function While Creating a Unique List

We can also use the IFERROR function behind the UNIQUE function to display a customized message if any error is found in the return value. Lets assume, we want to know the names of the unique participants who are under 21. As there is no instance of any participant under 21, the formula should return a #N/A error. But instead of showing this error, well rather use the IFERROR function to display a customized message- Not Found.

So, the required formula in Cell F5 will be:

After pressing Enter, the formula will return the specified message as shown in the following screenshot.

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

Now well get rid of the UNIQUE function and apply the combination of INDEX and MATCH functions. The INDEX function returns a value or reference of the cell at the intersection of the particular row and column. And the MATCH function returns the position of an item that matches the specified value in a specified order. Assuming that we want to know the unique names of the participants who took part in swimming only.

Step 1:

Select the output Cell F5 and type:

Press Enter.

Youll get the first unique name as the return value.

Step 2:

Use the Fill Handle to drag the cell to the right.

And youll find the age of the participant too.

Step 3:

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

So, this is how you can extract unique data based on a given condition with the 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.

8. Prepare a Unique List in Excel Based on Multiple Criteria

In this section, well find out how the INDEX-MATCH formula works out when multiple conditions are applied. For example, we want to know the unique names of the participants who have participated in swimming and are less than 25 years old.

The required formula in the output Cell F5 will be:

After pressing Enter and auto-filling the new cells in the output column, well get the unique names as shown in the picture below.

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.

9. Make Multiple Unique Lists Along Rows and Columns with Criteria

In our last method, well use an Excel table to extract multiple unique lists of data. In the following picture, the table at the bottom will show the unique names of the participants based on the sports types.

Weve named the table or the range of cells (B5:C13) with 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.

Step 1:

The required formula in the first output Cell B16 will be:

After pressing Enter, the first output value will return at once.

Step 2:

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

Step 3:

Now copy the range of cells from the outputs (B16:B20) in the first column.

In Cell C16, paste the values with the Formulas (F) option.

Youll get the second column with the unique names of the participants who took part in cycling only.

Step 4:

Similarly, paste the copied values as formulas again in Cells D16 and E16.

And youll 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 wont be able to extract the original output.

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, its 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.

Concluding Words

I hope all the methods illustrated above will now help you to apply them in your Excel spreadsheets more effectively while creating a list of unique values from a data table. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

You May Also Like to Explore

How to Populate a List Based on Cell Value in Excel (6 Ways)

Conditional Drop-Down List in Excel (Create, Sort, and Use)

How to Create Dynamic Dependent Drop Down List in Excel

VLOOKUP To Compare Two Lists (Same or Different Sheets)

Excel Top 10 List with Duplicates