UNIQUEBYPATTERN function

Created by Harry Lewis, Modified on Thu, 5 Oct, 2023 at 5:49 PM by Gabriel Michaud

Applies to:

Velixo Classic and Velixo Nx

Description

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.

TABLE OF CONTENTS

Description
Syntax
Parameters
Example

Syntax

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

Parameters

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

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:

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)

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