# Write All Values of the Non-Grouping Field in One Row

Problem description & analysis

Below is Excel file Book1.xlsx:

 Account Name Join Date Other Columns Package Account 1 2001/1/19 Data Main Package Account 1 2001/1/19 Data Bolt On Account 1 2001/1/19 Data Add on Account 2 2001/1/18 Data Main Package Account 3 2001/1/17 Data Main Package Account 3 2001/1/17 Data Add on Account 4 2001/1/19 Data Main Package

We are trying to group the file by Account Name and write all unique values in one row, as shown below:

 Account Name Join Date Other Columns Package Extra Item Extra Item Account 1 2001-01-19 Data Main Package Bolt On Add on Account 2 2001-01-18 Data Main Package Account 3 2001-01-17 Data Main Package Add on Account 4 2001-01-19 Data Main Package

Solution

Write the following script p1.dfx in esProc:

 A 1 =file("Book1.xlsx").xlsimport@w() 2 =A1.m(2:).group(~(1)).(if(~.len()>1,~=~.m(1)|~.m(2:).(~(4)),~=~.m(1))) 3 =A1.m(1).pad("Extra   Item",A2.max(~.len())) 4 =file("result.xlsx").xlsexport@w([A3]|A2)

Explanation:

A1   Import data from the Excel file and return it as a sequence of sequences.

A2  Retrieve data from the second row and group rows by the first column (Account Name). If the current group contains more than one row, append values of the fourth column (Package) beginning from the second row to the first row in order, otherwise just get the first row.

A3  Fill empty columns in the first row (column heading) in A1 with string "Extra Item" (to the maximum number of columns).

A4  Concatenate headers and detailed data and export result to result.xlsx.