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)
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/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
