...

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

 

Problem description & analysis

Below is Excel data:

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/1/19

Data

Main Package

Solution

Use the SPL XLL plug-in

Write the formula in a blank cell:

=spl("=((a=?.m(2:).group(~(1)).(if(~.len()>1,~=~.m(1)|~.m(2:).(~(4)),~=~.m(1))),b=?.m(1).pad(""Extra Item"",a.max(~.len())),[b]|a))",A1:D8)

As picture:

..

Return:

..

Explanation:

a: 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.

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

Then concatenate headers (b) and detailed data (a) get the results

Q & A Collection

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


 
 •  309 View  •