Creating a Sequence Number based on Multiple Columns
The Auto Number tool is a handy utility. This tip of the day goes through the very simple process of using Auto Number to create a sort index based on the value in multiple columns.
Assume your data looks like this. There is a Group column with values A, B and C. There is also a GroupSeqNo column which contains the sort order values to be applied within each group value.
Your goal is to order the rows based on a sort of both Group and GroupSeqNo.
Follow these steps to create a new column with sequential values aligned to sort conditions applied to multiple columns.
- Use the Auto Number tool to invoke the Auto Number editor.
- In the Auto Number editor, give the new column a name such as “Sequence” in the illustration below.
- Next, click on the column header name for the “Group” column. This will sort the data by Group
A confirmation that the data is sorted by Group appears in the Auto number editor.
- Now the data is sorted by Group, hold down the shift key and click the column header called “GroupSeqNo”. This will sort the data by GroupSeqNo within Group. (there is an X icon to the right in case you want to change your sort order)
- Click Save. Notice the Sequence column has unique, sequential values aligning to the goal of sorting the data by Group then GroupSeqNo. Also notice that when the Auto Number step is highlighted, the sort columns are listed in the step and Sort Indicators provide a visual clue as well.
That’s it! A very simple task that can move your data prep project along nicely.