Google Sheets – Create a Unique List from Multiple Columns

This post demonstrates how you can derive a single-column, distinct list of values from multiple columns of values in Google Sheets.

Creating such a list in “the Old Days” was accomplished using complex array syntax.

Today, we can create just such a list easily with a lesser-known Google Sheets function called FLATTEN.

We start with a list of Apps that are used by various Regions.

Our objective is to create a distinct, single-column list of Apps that are derived from the multi-column list above.

Let’s Try the UNIQUE Function

At this point, many users have become acquainted with the UNIQUE function.

The UNIQUE function returns unique rows in the provided source range, discarding duplicates.  Rows are returned in the order in which they first appear in the source range.

For a more detailed explanation of the UNIQUE function along with some examples of its use, check out this Google Docs link to the UNIQUE function.

Google Sheets – UNIQUE Function

To demonstrate the process of generating a unique list, let’s start with a single column example.

We write the below UNIQUE function to create a unique list of Apps located in “Europe”.

=UNIQUE(A2:A12)

Using Values Across Columns

If your values are listed in a single row (i.e., spanning multiple columns), you can use an additional argument in the UNIQUE function to tell it to “look” across columns instead of across rows.

=UNIQUE(A2:E2, TRUE)

“Can we use rows and columns at the same time?”

Now let’s see if we can feed the UNIQUE function a matrix of values.

Enter the following formula that uses range A2 through E13 as the source values.

=UNIQUE(A2:E12)

We see that it fails to produce the desired result.

The reason this fails is that UNIQUE is treating all the values of a row as a single criterion.  In other words, if we see the values on Row 2, we are trying to find another row with “deRambler, Fightrr, Kryptis, Perino, deRambler”.

Because no other row has that same series of Apps, Row 2 is considered to be unique amongst the other rows.

If UNIQUE can’t handle the task, perhaps a different function may be able to.

Google Sheets Course Leila Gharani

This Is The ONLY Google Sheets Course You Will Ever Need!

This Is The ONLY Google Sheets Course You Will Ever Need! Because it’s going to teach you everything you need to know: From data collection, automation, to proper data analysis and visualization.

GET ACCESS

Enter the FLATTEN Function

The FLATTEN function flattens all the values from one or more ranges into a single column.

For a more detailed explanation of the FLATTEN function along with some examples of its use, check out this Google Docs link to the FLATTEN function.

Google Sheets – Flatten Function

To see how FLATTEN would react when we feed it the same data range as our last attempted UNIQUE formula, write the below formula.

=FLATTEN(A2:E12)

We see the following result.

Reducing the Flattened List to be Unique

Although we have stacked all the columns of Apps into a single column, we still have duplicates in the result.

Let’s bring the UNIQUE function back into the conversation to assist with removing the duplicate values from the list.

=UNIQUE(FLATTEN(A2:E12) )

Creating a Filtered & Distinct List

Suppose I wanted a distinct list of Apps that are derived only from North America and South America.

The FLATTEN function can accept multiple ranges as input for deriving distinct lists.

=FLATTEN(B2:B12, D2:D12)

Now let’s remove the duplicates of the fully stacked list of Apps by placing the FLATTEN function inside a UNIQUE function.

=UNIQUE(FLATTEN(B2:B12, D2:D12) )

Removing the Blank Cells

Because empty cells are treated as unique entries, we can filter out the blank cells by placing the above formula in a FILTER function.

The FILTER function will accept the range and filter to not include blank cells.  This is defined with a “<>” (not equal to) operator and two sets of double quotes.  The two sets of double quotes mean “empty text” to most computer applications.

=UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) )

NOTE: You could also have written the formula as follows and it would behave in the same manner.

=FILTER(UNIQUE(FLATTEN(B2:B12, D2:D12) ), FLATTEN(B2:B12, D2:D12) <> “” )

Sorting the Results

For our final step of making the perfect list of unique values, let’s sort the list alphabetically from A to Z.

Place the previous formula within a SORT function.

=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) ) )

If you wanted the list to be sorted in Z to A order, you would need to write the formula as follows.

=SORT(UNIQUE(FILTER(FLATTEN(B2:B12, D2:D12), FLATTEN(B2:B12, D2:D12) <> “” ) ) , 1, FALSE)

MASTER GOOGLE SHEETS

GET THE COMPLETE COURSE

Learn anytime that fits your schedule.

Complete the Challenges. Practice. Apply.

Get Access