...

Align Data in Specified Order

 

Example

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:

A

1

=T("E:/work/book1.xlsx")

2

=26.(char(~+96))

3

=A1.align(A2,Area)

4

=A3.new(A2(#):Area,Date)

5

=T("E:/work/book2.xlsx",A4)

 

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

 

 


 
 •  322 View  •