Functions Utility

AlignToAxes

July 19, 2021November 26, 2025 by Help

AlignToAxes() can be used to transpose only the source data that matches the specified target data. The target data does not have to be contiguous but can contain spaces between rows and columns that do not get overwritten.

Syntax:

Qx.AlignToAxes(Range, Rows, Columns, View, Row Axis Columns, Column Axis Column, Value Column)

In Excel with Quintessence add-in versions prior to 25.1, use: AlignToAxes()
In the Quintessence Editor (Studio) using Quintessence language, use: AlignToAxes()
In the Quintessence Editor (Studio) using Python language, use: Qx.AlignToAxes()

ⓘ See parameter descriptions

Range	The data that needs to be mapped.
Rows	The axis representing rows.
Columns	The axis representing columns.
View (optional)	Defines how the data should be displayed. Options include: • `none` return result without headers • `column` return result with column headers only • `row` return result with row headers only • `both` return result with both column and row headers
Row Axis Columns (optional)	The column to use for the row axis. This data must be in a vertical format.
Column Axis Columns (optional)	The column to use for the column axis. This data must be in a horizontal format.
Value Column (optional)	The column to use for the value.

Remarks:

Unless changed by an administrator, the default parameters are:
- View: none
- Row Axis Columns: Defaults to the first column of the Range parameter.
- Column Axis Columns: Defaults to the second column of the Range parameter.
- Value Column: Defaults to the third column of the Range parameter.

Examples:

The examples provided below are based on fictitious data for illustrative purposes only.

> Example 1: Rearrange the source data that matches the target criteria

The following source data is in Excel in range B2:D13:

Stock 1	Monday, March 31, 2014	250
Stock 1	Monday, June 30, 2014	300
Stock 1	Tuesday, September 30, 2014	100
Stock 2	Monday, March 31, 2014	240
Stock 2	Monday, June 30, 2014	200
Stock 2	Tuesday, September 30, 2014	90
Stock 3	Monday, March 31, 2014	110
Stock 3	Monday, June 30, 2014	350
Stock 3	Tuesday, September 30, 2014	225
Stock 4	Monday, March 31, 2014	95
Stock 4	Monday, June 30, 2014	100
Stock 4	Tuesday, September 30, 2014	400

The rows to display the aligned results is in range F4:F6:

Stock 1

Stock 2

Stock 4

The columns to display the aligned results is in range F2:H2:

Monday, March 31, 2014

Monday, June 30, 2014

Tuesday, September 30, 2014

To align this data with both the row and column headers showing, the following formula can be passed:

=Qx.AlignToAxes(B2:D13, F4:F6, F2:H2, "both", "C1", "C2", "C3")

Result is:

	Monday, March 31, 2014	Monday, June 30, 2014	Tuesday, September 30, 2014
Stock 1	250	300	100
Stock 2	240	200	90
Stock 4	95	100	400

> Example 2: Rearrange the source data that matches multi-level target criteria

The following source data is in Excel in range B2:E13:

Stock 1	Price	Monday, March 31, 2014	250
Stock 1	Quantity	Monday, March 31, 2014	100
Stock 1	Price	Monday, June 30, 2014	240
Stock 1	Quantity	Monday, June 30, 2014	105
Stock 2	Price	Monday, March 31, 2014	15
Stock 2	Quantity	Monday, March 31, 2014	1000
Stock 2	Price	Monday, June 30, 2014	15
Stock 2	Quantity	Monday, June 30, 2014	1005
Stock 3	Price	Monday, March 31, 2014	200
Stock 3	Quantity	Monday, March 31, 2014	100
Stock 3	Price	Monday, June 30, 2014	200
Stock 3	Quantity	Monday, June 30, 2014	55

The rows to display the aligned results is in range G4:H5 (a blank row will be used to separate the data between Stock 1 and Stock 3):

Stock 1	Price
Stock 1	Quantity

Stock 3	Price
Stock 3	Quantity

The columns to display the aligned results is in range G2:H2: