Dynamic Array - Unique Sort
We start from the last article on XLOOKUP (https://bit.ly/39dN8yq) and try to solve the problem of extracting unique data and sorting them using the new dynamic array functions. Bonus, an optimization of data validation. This article is for Office 365 users only.
Here is the file used for example:
http://bit.ly/2UZAEGT
Problem 1. Extract unique data. For example, the product list.
We want to make a data extraction form (XLOOKUP) where we find out the quantity delivered for a particular product.
In G4 we will introduce a validation list with product names.
For this list we can use (in classic version): Copy column Den_P - Paste to a nearby location (eg column K) - from Data, Remove Duplicates
From Data - Data Validation - Allow - List, Source - select the extracted data.
For a more professional variant, instead of selecting the data we introduce the formula:
= OFFSET ($ K $ 2; 0; 0; count ($ K: $ K) 1)
Or, in the Dynamic Array version, in M2 we introduce the formula:
= UNIQUE (Table1 [Den_P])
The result obtained differs slightly in appearance (when selecting a cell, the area is delimited and displayed shaded). This mode is called Spill. This area can only be edited in the first cell. The deletion of this cell entails the deletion of the entire area.
As can be seen, both lists obtained are not sorted for easier use. This generates the second problem: data sorting.
Problem 2. Sorting data
In the classic version the sorting would be done manually.
In Dynamic Array, we will use the SORT function. By default, sorting is in ascending order.
= SORT (UNIQUE (Table1 [Den_P]))
Sort_index, Sort_order and By_col are optional.
Sort_index - sort by a particular column, if the area is a data table. The default is 1.
Sort_order - sorting ascending (default, value 1), respectively decreasing, value -1
By_col - sort by lines (default) or by columns
Ex. Previous function sorted by descending = SORT (UNIQUE (Table1 [Den_P]); 1; -1)
Bonus! Previously I used an OFFSET for a dynamic validation list. With Dynamic Array use = $ M $ 2 # where M2 is the first cell in the previously obtained spill.
Much simpler! Much faster!
For the next time the FILTER function (data extraction based on a condition)
Here is the file used for example:
http://bit.ly/2UZAEGT
Problem 1. Extract unique data. For example, the product list.
We want to make a data extraction form (XLOOKUP) where we find out the quantity delivered for a particular product.
In G4 we will introduce a validation list with product names.
For this list we can use (in classic version): Copy column Den_P - Paste to a nearby location (eg column K) - from Data, Remove Duplicates
From Data - Data Validation - Allow - List, Source - select the extracted data.
For a more professional variant, instead of selecting the data we introduce the formula:
= OFFSET ($ K $ 2; 0; 0; count ($ K: $ K) 1)
Or, in the Dynamic Array version, in M2 we introduce the formula:
= UNIQUE (Table1 [Den_P])
The result obtained differs slightly in appearance (when selecting a cell, the area is delimited and displayed shaded). This mode is called Spill. This area can only be edited in the first cell. The deletion of this cell entails the deletion of the entire area.
As can be seen, both lists obtained are not sorted for easier use. This generates the second problem: data sorting.
Problem 2. Sorting data
In the classic version the sorting would be done manually.
In Dynamic Array, we will use the SORT function. By default, sorting is in ascending order.
= SORT (UNIQUE (Table1 [Den_P]))
Sort_index, Sort_order and By_col are optional.
Sort_index - sort by a particular column, if the area is a data table. The default is 1.
Sort_order - sorting ascending (default, value 1), respectively decreasing, value -1
By_col - sort by lines (default) or by columns
Ex. Previous function sorted by descending = SORT (UNIQUE (Table1 [Den_P]); 1; -1)
Bonus! Previously I used an OFFSET for a dynamic validation list. With Dynamic Array use = $ M $ 2 # where M2 is the first cell in the previously obtained spill.
Much simpler! Much faster!
For the next time the FILTER function (data extraction based on a condition)
