Task description

book1.xlsx stores multiple pairs of start date and end date of certain courses, as shown below:

We want to sort the start dates for each course and list the most recent start date and end date (values highlighted in yellow in the above). Here’s the expected result table:

Contrary to common row-wise tables, both the source table and the target table stores data in a column-wise way.

Directions:

1.Write script:

A1   Import data of book1.xlsx. @w option enables reading data as a sequence of sequences, as shown below:

A2   Perform transposition over A1’s sequences to get the following result:

A3   Create a table sequence where column headers are members of A2’s first sequence, and populate members of other sequences of A2 in order to form records.

A4   Group records of A3 by Courses and get the record having the most recent date from each group.

A5   Join each of A4’s records into an array preceded by its column header, as shown below:

A6   Perform transposition on A5’s sequence.

A7   Export the result to book2.xlsx. @w option enables exporting data as a sequence of sequences

2. Press F9 to execute the program. Then you can open the target file to view the result table.

【Reference】 colsort.zip