RaqForum 43 No.
350 View  • 
     ...

Excel Group Rows and Concatenate Each Value of a Specific Column into a String

 

Problem description & analysis

We have an Excel data as follows:

Session FirstName LastName Email Address
1 Bob Smith bob@1.com Main Street
2 Bob Smith bob@1.com Main Street
4 Bob Smith bob@1.com Main Street
1 Frank Jones Frank@2.com 2nd Street
2 Frank Jones Frank@2.com 2nd Street
3 Frank Jones Frank@2.com 2nd Street
4 Frank Jones Frank@2.com 2nd Street
1 Henry Andrews henry@3.com 3rd Street
2 Henry Andrews henry@3.com 3rd Street

We are trying to group the file by Email and concatenate each Session value into a string by comma. Below is the desired result:

Email FirstName LastName Address Session
Frank@2.com Frank Jones 2nd Street 1,2,3,4
bob@1.com Bob Smith Main Street 1,2,4
henry@3.com Henry Andrews 3rd Street 1,2

Solution

Load SPL XLL plug-in

Enter the following formula in a blank cell

=spl("=E(?).group(Email;FirstName,LastName,Address,~.(int(Session)).concat@c():Session)",A1:E10)

As picture:

..

Return the results:

..

Explanation:

Group the table by “Email”, retain all columns except for “Session”, and convert each “Session” value into a comma-separated string.

Q & A Collection

https://stackoverflow.com/questions/63623624/merge-excel-rows-based-on-single-field


 
 •  350 View  •