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

Judith-Excel-Sharing - Aug 28 - - Dev Community

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.

original table
Below is the expected standard format table:

desired 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:

result table with code entered
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.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .