Velixo recommends the use of Microsoft 365 for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisites for additional information.
The UNIQUEBYPATTERN function returns the unique values (based on a pattern) from a range or array.
UNIQUEBYPATTERN(Array [or range], Pattern, Column)
The UNIQUEBYPATTERN function uses the following parameters (see our article on including multiple parameter values):
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.
The pattern to extract from the specified Column, using # to denote characters to include and ? to denote characters to exclude.
The column from the Array or Range which contains the values to which the Pattern is to be applied.
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)
=UNIQUEBYPATTERN(C3#, "###???", 2)
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).