#53 - Get Desired Data Every N Rows And Combine Them Into One Row

Problem description & analysis:

We have an Excel table in a 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 1^st column of the 1^st row or the 1^st column of the 2^nd row (the two have the same values and just get one of them), the 2^nd column of the 2^nd row, and the 3^rd column of the 1^st 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

Solution:

Use SPL XLL to do this:

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

As shown in the picture below:

Explanation:

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 1^st column of the 1^st row in the current group.

Download esProc Desktop and boost your Excel skills with this powerful tool! ⬇️

SPL download address: esProc Desktop Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso