...

Convert a Crosstab to a Row-wise Table

 
excel(99) transpose(9)

Task description

Below is part of the data in crosstab table book1.xlsx:

undefined

We want to convert it to a row-wise table shown below:

undefined

 

Directions:

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2.  Write script in esProc:

undefined

We write the program separately:

A

1

=file("E:/pivot/book1.xlsx").xlsimport@t()

2

=A1.pivot@r(Names,Sales;Weekday,Day)

3

=A2.new(Names,Sales,Day).select(Day)

4

=file("E:/pivot/book2.xlsx").xlsexport@t(A3)

A1   Import data of book1.xlsx; @t option enables reading the first row as column headers.

A2   Perform transposition on A1’s data by grouping rows by Names and Sales, and converting other column names to Weekday values and other column values to Day values. @r option enables a column-to- row transposition.

A3   Get records made up of Names column, Sales column and Day column and where the Day value is not empty from A2.

A4   Store A3’s result in book2.xlsx.

 

3. Press F9 to execute the script. You can view the target Excel table in book2.xlsx after execution is finished.

 

 

【Reference】 pivot.zip


excel(99) transpose(9)
 
 •  289 View  •