COLLATE function

Created by Harry Lewis, Modified on Thu, 30 Nov, 2023 at 2:31 PM by Harry Lewis

Applies to

  • Velixo Classic
  • Velixo NX

 

Description


The COLLATE function combines multiple arrays or ranges into a single array.

 

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

 

TABLE OF CONTENTS


Overview

 

The COLLATE function supports any valid Excel reference. Including:

 

  • An Excel 365 spill range (ex: A1#)

  • A standard range (ex: N13:N15)

  • An individual cell (ex: N15)

  • A direct value (ex: "Hello")

  • An inline array of rows or columns:

    • Rows are separated by semicolons: {"Row A" ; "Row B"}

    • Columns are separated by commas: {"Column A" , "Column B"}



The arrays and ranges do not have to contain the same number of columns; the resulting array will be as wide as the widest range passed to COLLATE()

  

 

Syntax

  COLLATE(Array [or range], Array [or range]) 

  

 

Parameters

The COLLATE function uses the following parameter:

Parameter

Required/Optional

Description

Arrays [or ranges]

Required

The arrays and/or ranges to be combined

(note that, by nature, the function uses multiple arrays and/or ranges)

 

Examples

Example 1

Let us consider an Excel 365 worksheet with two arrays (one defined in cell A1, the other defined in cell A13):

 

union1.png

 

 

Using the COLLATE function...

 

=COLLATE(A1#,A13)

...we can combine them into one array:

 

union2.png

 
Example 2

Given those same arrays, plus some additional data in column N:

 

union3.png

Using the COLLATE function, we can combine all of those, plus additional data:

 

=COLLATE(A1#,A13,N13:N15,"A single value",{"Row A";"Row B"},{"Column A","Column B"})

 

union4.png




Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article