How to select data from list using Query with UNIQUE function in google sheet?

FITFLOP

2 min read

·

01-10-2024

5

0

Share

Extracting Unique Values from a List in Google Sheets with QUERY and UNIQUE

Need to extract a list of unique values from a column in your Google Sheet? The QUERY and UNIQUE functions work together to make this task a breeze. Let's break down how to do it.

Scenario: Imagine you have a list of products in column A, and some items are repeated. You want to create a new list containing only the unique products.

Original Code:

=UNIQUE(QUERY(A:A, "select A"))

Explanation:

1. QUERY(A:A, "select A"): This part of the formula selects all values from column A. The QUERY function is powerful, allowing you to filter, sort, and manipulate data within your spreadsheet.
2. UNIQUE(): The UNIQUE function takes the output of the QUERY function and removes any duplicate values, returning a list of only unique entries.

Breaking Down the Formula:

• QUERY(A:A, "select A"): This part is crucial for understanding how to use QUERY to extract data from a column. The syntax for QUERY is QUERY(data, query, [headers]). Here:

  • data: The range of data you want to query. In this case, it's A:A (entire column A).
  • query: This is where you write the SQL-like query language to specify what data to extract. select A instructs the function to select all values from the first column (column A).
  • [headers]: This is an optional argument to specify the headers for the query results. We are omitting it here.

• UNIQUE(): This function is simple and straightforward. It takes an array as input (in this case, the result of the QUERY) and returns only the unique values.

Putting It into Practice:

Let's say you have a list of fruits in column A:

Apple
Banana
Orange
Apple
Orange
Strawberry

After applying the formula =UNIQUE(QUERY(A:A, "select A")), your new list will show:

Apple
Banana
Orange
Strawberry

Additional Tips and Use Cases:

• Selecting Unique Values from Multiple Columns: You can use QUERY to select multiple columns and then use UNIQUE to extract unique values from those columns. For example, =UNIQUE(QUERY(A:C, "select A, B")) would return unique combinations of values from columns A and B.
• Filtering and Extracting Unique Values: You can combine QUERY with UNIQUE to filter data and extract unique values. For example, =UNIQUE(QUERY(A:A, "select A where A = 'Apple'")) would return only the unique "Apple" values from column A.
• Understanding the Limitations: The UNIQUE function only works with one-dimensional arrays, meaning you can't directly apply it to a two-dimensional range containing multiple columns. You'll need to use QUERY or other methods to extract data from multiple columns first.

By leveraging the power of QUERY and UNIQUE, you can easily extract distinct values from your Google Sheet data, making data analysis and organization much more efficient!