More

# Which formula would provide a dynamic array showing a unique list of categories in a products table where the group is “clothing”?

Answer: `=FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")`

In Microsoft Excel, dynamic arrays enable users to work with multiple values simultaneously, simplifying the process of creating and managing lists or tables. One of the most powerful features of dynamic arrays is their ability to generate dynamic results based on formulas. In this explanation, we will examine a formula that provides a dynamic array showing a unique list of categories in a products table where the group is “clothing.” This formula combines two functions: `FILTER()` and `UNIQUE()`.

## Dynamic Arrays and Functions

### Dynamic Arrays

Dynamic arrays are a powerful feature in Excel that allows formulas to output multiple values simultaneously. Instead of outputting a single value, dynamic arrays “spill” the result into a range of cells, making it easy to work with large amounts of data. The size of the array adjusts automatically based on the formula and the source data, ensuring that the output remains up-to-date as the data changes.

### FILTER() Function

The `FILTER()` function is a dynamic array function that filters a range or an array based on the criteria specified in the formula. It returns a new array containing only the rows or columns that meet the given conditions. The syntax for the `FILTER()` function is as follows:

`=FILTER(array, include, [if_empty])`
• `array`: The range or array to filter.
• `include`: The criteria to apply to the array. The function will return only the rows or columns for which this argument evaluates to TRUE.
• `if_empty`: (Optional) The value to return if no rows or columns meet the criteria. If this argument is not provided, the function will return an error if no rows or columns meet the criteria.

### UNIQUE() Function

The `UNIQUE()` function is another dynamic array function that returns a unique list of values from the specified range or array. It removes duplicate values and returns only distinct elements. The syntax for the `UNIQUE()` function is as follows:

`=UNIQUE(array, [by_col], [exactly_once])`
• `array`: The range or array from which to extract unique values.
• `by_col`: (Optional) A logical value that specifies whether the unique values should be extracted by row or by column. If TRUE, the function will return unique values by column; if FALSE or omitted, the function will return unique values by row.
• `exactly_once`: (Optional) A logical value that specifies whether to return only values that occur exactly once in the array. If TRUE, the function will return only values that occur exactly once; if FALSE or omitted, the function will return all unique values.

## The Formula

To create a dynamic array showing a unique list of categories in a products table where the group is “clothing,” we can use the following formula:

`=FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")`

This formula combines the `FILTER()` and `UNIQUE()` functions to generate the desired output. Let’s break down the formula step by step to understand how it works:

1. `UNIQUE(productsTable[Category])`: This part of the formula extracts a unique list of categories from the `Category` column in the products table. The `UNIQUE()` function ensures that the list contains only distinct category values, eliminating any duplicates.
2. `productsTable[Group]="clothing"`: This part of the formula serves as the criteria for the `FILTER()` function. It checks if the `Group` column in the products table contains the value “clothing.” The `FILTER()` function will return only the rows for which this condition is TRUE.
3. `=FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")`: Combining the two parts, this formula first obtains a unique list of categories using the `UNIQUE()` function, and then filters the resulting array based on the specified criteria (“clothing” group) using the `FILTER()` function. The output is a dynamic array containing a unique list of categories in the products table where the group is “clothing.”

## Example

Let’s consider a hypothetical products table with the following columns: `ProductID`, `ProductName`, `Category`, `Group`, and `Price`.

ProductID ProductName Category Group Price
1 Shirt Tops Clothing 25
2 Pants Bottoms Clothing 35
3 Jacket Outerwear Clothing 50
4 Shoes Footwear Clothing 60
5 Dress Dresses Clothing 45
6 Hat Accessories Clothing 15
7 Blender Kitchen Appliances 100
8 Toaster Kitchen Appliances 30

In this table, we want to create a dynamic array showing a unique list of categories where the group is “clothing.” Using the formula `=FILTER(UNIQUE(productsTable[Category]), productsTable[Group]="clothing")`, we get the following output:

Unique Categories
Tops
Bottoms
Outerwear
Footwear
Dresses
Accessories

This dynamic array displays a unique list of categories where the group is “clothing,” as required.

Disclaimer: While we make every effort to update the information, products, and services on our website and related platforms/websites, inadvertent inaccuracies, typographical errors, or delays in updating the information may occur. The material provided on this site and associated web pages is for reference and general information purposes only. In case of any inconsistencies between the information provided on this site and the respective product/service document, the details mentioned in the product/service document shall prevail. Subscribers and users are advised to seek professional advice before acting on the information contained herein. It is recommended that users make an informed decision regarding any product or service after reviewing the relevant product/service document and applicable terms and conditions. If any inconsistencies are observed, please reach out to us.