Align Data in Specified Order



The Excel file book1.xlsx has some data as shown in the figure below:


The task is to sort the Area column in the alphabetical order of a-z, and if there is no value corresponding to the ordinal letter, the Date column will be set as empty, as shown in the following figure:


Write SPL script:












A1 Read the data of book1.xlsx


A2 Generate a letter sequence of a-z


A3 Align the data of Area column in A1 in the order of A2, and fill in as blank rows if there is no data corresponding to the letter in A2


A4 Use A3 to create a new data set, take the letter corresponding to row number # in A2 as the value of the new Area column, and take out the value of Date column in A3


A5 Store A4 to book2.xlsx