This Analytics Edge wizard is used to rearrange and/or rename columns in a table.
Typically used to change the column order or names for a report. It can also be used to make column names consistent with other tables so that comparison functions like Compare, Match, or Combine can recognize matching columns.
Blog article: Using the Arrange Function
By Position
Use this option to rearrange the columns based on the original column positions (Excel columns A, B, C, etc).
Column Names and Order – The columns in the table are listed with their original column letter (A, B, C, etc) for reference. Select a column to modify (it will be highlighted in yellow), then select one of the actions:
Move selected up – move the selected column up in the list.
Remove selected column – remove the selected column from the listing. Use Reset to get back to the original columns if necessary.
Move selected down – move the selected column down in the list.
Rename – select a column in the list, then enter a new column name in the text box beside the button and click the button to assign a new name to the selected column.
Reverse – completely reverse the order of all of the columns in the list from top to bottom.
Reset – reset the list of columns back to the original. This removes all the changes previously made.
By Name
Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.
Column Names and Order – The columns in the table are listed by name. Select a column to modify (it will be highlighted in yellow), then select one of the actions.
The options on this tab are similar to By Position above.
Stop macro if column missing – is used to stop process of the macro if a named column is missing in the table. This is useful to verify downloaded or imported data before processing.
Append – select a column in the list, then enter a new column name in the text box beside the button and click the button to create a new column following the selected column. This can be used to force certain columns in a report even if the source data may not contain those columns.
Insert Column
Use this option to insert a new (empty) column with a specific name at a specific location. The new column is inserted at the selected location, so the column selected is shifted to the right.
Before/After – insert the new column before or after the selected column.
By Name – use the name of the selected column instead of the column letter (position).
New Column Name – the title for the new empty column. You can use the button at the end to use an Excel cell reference to get the value.
New Column Value – used to pre-populate the entire column with a specific text, numeric or date value. Select the type of value (column) you want: text, number or date. You can use the button at the end to use an Excel cell reference to get the value. [new in v10] Another option is the Row Number, which inserts a series of numbers starting at 1.
Delete Column
Use this option to delete one or more columns in the current table.
By Name – allows you select the column by name instead of position.
Sort by Name
Use this option tab to sort the columns by the names of the columns themselves. Select whether to sort alphabetically, numerically, or by date/time order.
Date/time sorting will recognize several date formats depending on the locale settings of your computer.
Sort Order can be selected as ascending or descending.
MultiRow Records
Downloads and file imports will typically create one row per record, but if a record contains multiple items, like a phone number, they may be loaded into separate columns. This function will align columns with the same name (but having -1, -2, -3 endings), but will result in multiple rows for a single record.
Updated for v10.