Google Sheets: Create Unique List Across Multiple Columns


You can use the following formulas to create a unique list of values across multiple columns in Google Sheets:

Formula 1: Create Unique List of Values (Display Across Multiple Columns)

=UNIQUE(A2:C20)

Formula 2: Create Unique List of Values (Display in One Column)

=UNIQUE(FLATTEN(A2:C20))

The following examples show how to use each formula in practice.

Example 1: Create Unique List of Values (Display Across Multiple Columns)

Suppose we have the following dataset that contains information for various basketball players:

We can use the following formula to return only the unique rows across both columns:

=UNIQUE(A2:B11)

The following screenshot shows how to use this formula in practice:

Notice that the formula only returns the unique rows from the original dataset.

Example 2: Create Unique List of Values (Display in One Column)

Suppose we have the following dataset that contains the positions of players on various basketball teams:

We can use the following formula to find the unique values across all three columns and display the results in one column:

=UNIQUE(FLATTEN(A2:C6))

The following screenshot shows how to use this formula in practice:

Notice that the formula only returns the unique positions across each of the three columns.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Use COUNTIF with Multiple Ranges in Google Sheets
How to Use SUMIF with Multiple Columns in Google Sheets
How to Sum Across Multiple Sheets in Google Sheets

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *