UNIQUEBYPATTERN

Overview

The UNIQUEBYPATTERN function returns the unique values (based on a pattern) from a range or array.

Velixo recommends the use of Microsoft 365 or Excel 2021 or higher for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisitesfor additional information.

Syntax

CODE

=UNIQUEBYPATTERN(Array [or range], Pattern, Column)

Arguments

The UNIQUEBYPATTERN function uses the following arguments (see our article on filtering Velixo functions):

Argument	Required/Optional	Description
`Range`	Required	When referencing a single function that "spills" data into multiple cells (an array), this is a reference to the cell containing the function and must include the # suffix. If referencing a range of cells (not tied to a single function) this is an Excel range.
`Pattern`	Required	The pattern to extract from the specified `ColumnIndex`, using # to denote characters to include and ? to denote characters to exclude.
`ColumnIndex`	Required	The column from the `Range` which contains the values to which the `Pattern` is to be applied.

Example

Here is a list of accounts and related subaccounts:

For our example, what we really want to see is the first segment (the first three characters) of the related subaccounts:

(and we only want the unique values - no duplicates)

CODE

 =UNIQUEBYPATTERN(C3#, "###???", 2)

Description
Examines the second column of the array created by the function in cell C3 and extracts the first three characters, examines the values in the second column and applies the pattern (extracting the first three characters, but ignoring the last three characters).

Note: the # sign used for the array (C3#) allows the array to change sizes while the UniqueByPattern function adjusts the size, automatically. For other applications a standard range reference could be used (e.g., C3:D18).

Results