RaqForum 28 No.
370 View  • 
     ...

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

 
excel(115) column-to-row transposition(2)

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.

Q & A Collection

https://stackoverflow.com/questions/63641240/move-duplicate-rows-unique-value-to-new-column


excel(115) column-to-row transposition(2)
 
 •  370 View  •