#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 1st column of the 1st row or the 1st column of the 2nd row (the two have the same values and just get one of them), the 2nd column of the 2nd row, and the 3rd column of the 1st row.

Below is the expected standard format table:

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