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

Description

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

Syntax

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

The UNIQUEBYPATTERN function uses the following parameters (see our article on including multiple parameter values):

Parameter

Required/Optional

Description

Array

[or 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 Column, using # to denote characters to include and ? to denote characters to exclude.

Column

Required

The column from the Array or Range which contains the values to which the Pattern is to be applied.

Example

Here is a list of accounts and related subaccounts:

unique01.png

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

unique02.png

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

=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

unique03.png
Did this answer your question?