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. This is useful for formatting reports.

Syntax

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

Examples

Example 1. Rearrange the source data that matches the target criteria

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

Example 3. Rearrange the source data into flexible output formats

Example 4. Rearrange the source data into flexible output formats, but retain any information that already exists in the unmatched blocks

Example 1. Rearrange the source data that matches the target criteria

In this example, a range of data is transposed to form a new grid in a different arrangement. The source column ‘Asset’ is matched against the new rows. The source column ‘Date’ is matched against the new columns. The source column ‘Price’ is used as the grid value corresponding to the rows and columns.

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

This example has multiple level target rows, consisting of Portfolio and Date. The match is done on source column 1 (Portfolio) and source column 3 (Date).

Example 3. Rearrange the source data into flexible output formats

An additional advantage of this function is that spaces can be created between the rows and columns in the target output block. Those spaces can contain formatting, values and formulas, and the matching process can be instructed to disregard those spaces. In the example below, rows have been created in the output to create totals.

Note: In this example, the output overwrites (cleans up) anything within the contiguous output block. Sometimes this is desirable, but in this case, the totals need to be retained. In this example the second row of totals is not overwritten, as it falls outside of the contiguous output block, but the first row of totals is overwritten. To prevent information and formulas from being overwritten, see Example 4.

Example 4. Rearrange the source data into flexible output formats, but retain any information that already exists in the unmatched blocks

This example is identical to example 3 above, but the Fx() function is used to prevent any internal unmatched data from being overwritten. The AlignToAxes() function is placed inside the Fx() function, and Fx is provided with an additional parameter ‘TRUE’ which instructs it to retain internal unmatched information within the output block. Therefore the first row of totals is not overwritten.