TOTABLE Function

Created by Harry Lewis, Modified on Thu, 4 Apr at 2:58 PM by Harry Lewis

Applies to:

  • Velixo NX


The TOTABLE function converts an Excel array into an Excel table and places that table into the specified cell range.



TABLE OF CONTENTS


Syntax


TOTABLE(Array, KeyColumnIndex, DataIncludesHeader, OutputColumn1, OutputColumn2,...,OutputColumnN)


Parameters


The TOTABLE function uses the following parameters:


ParameterRequired/ OptionalDescription
ArrayRequiredThe array which is to be converted to a table.
KeyColumnIndexRequired if more than one Output Column is specified.if user-entered values (not those returned by a Velixo function) are be included in the resulting table, you must specify which columns in the input array represent unique keys (uniquely identify each row of the data).

If more than one column is required to uniquely identify each row, you can pass an inline array of column indexes.  This works the same as the Excel SORT() function - for example =SORT(A1#, {1,3}) would sort the array specified in cell A1 by column 1 and then by 3.
DataIncludesHeaderOptionalTrue
or
False (default)

Specifies whether or not the original array contains header information.
OutputColumn1RequiredIf no other Output Column is specified, this parameter contains the cell location where the table is to be placed.

If more than one Output Column is specified, this parameter specifies the cell in which the first column of the array is to appear within the table.
OutputColumn2OptionalIf more than one Output Column is specified, this parameter specifies the cell in which the second column of the array is to appear within the table.
...

OutputColumnNOptionalIf more than one Output Column is specified, this parameter specifies the cell in which the last column of the array is to appear within the table.





Examples


Example 1 - convert entire array to table (with columns in same order)

=TOTABLE(B10#,,,P9)


Description

Creates a table (based on the results of the array defined in cell B10) and places that table starting in cell P9.

 

Result




Example 2 - convert entire array to table (changing column order)

=TOTABLE(B10#, {2,1}, FALSE, K9, M9, I9, L9, N9, J9)


Description

Creates a table (based on the results of the array defined in cell B10) as follows:

  • The second and first columns of the original array (when combined) uniquely identify each row in the array.
  • Header information is assumed to not be included in the array defined in cell B10
  • The first column of the original array will be placed in column K
  • The second column of the original array will be placed in column M
  • The third column of the original array will be placed in column I
  • The fourth column of the original array will be placed in column L
  • The fifth column of the original array will be placed in column N
  • The sixth column of the original array will be placed in column J


 

Result


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