RaqForum 41 No.
318 View  • 
     ...

Get desired data every N rows and combine them into one row

 

We have an Excel table of non-standard format. Each range of 2 rows * 6 columns corresponds to 1 row * 3 columns of the standard format. The standard format table will consist of the 1st column of the 1st row or the 1st column of the 2nd row (the two have same values and just get one of them), the 2nd column of the 2nd row, and the 3rd column of the 1st row.


 A B C
1 John Doe Company A
2 John Doe john.doe@example.com
3 Jane Smith Company B
4 Jane Smith jane.smith@example.com
5 Alice Lee Company C
6 Alice Lee alice.lee@example.com
7 Bob Johnson Company D
8 Bob Johnson bob.johnson@example.com

Below is the expected standard format table:


 E F G
1 John Doe john.doe@example.com Company A
2 Jane Smith jane.smith@example.com Company B
3 Alice Lee alice.lee@example.com Company C
4 Bob Johnson bob.johnson@example.com Company D

Use SPL XLL to do this:

=spl("=?.group((#-1)\2).(~1(1)|~2(2)|~1(3))",A1:C8)

Picture1png

group()function groups table rows according to the specified rule; symbol # represents the row number; slash \ performs division and gets the integer part of the result; and ~1(1) is the 1st column of the 1st row in the current group.

Source:https://www.reddit.com/r/excel/comments/1chvluy/how_can_i_combine_rows_in_excel/


 
 •  318 View  •