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

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

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.

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

Print Share Comment Cite Upload Translate Updates

APA

Judith-Excel-Sharing | Sciencx (2024-08-28T02:47:26+00:00) #53 — Get Desired Data Every N Rows And Combine Them Into One Row. Retrieved from https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/

MLA

" » #53 — Get Desired Data Every N Rows And Combine Them Into One Row." Judith-Excel-Sharing | Sciencx - Wednesday August 28, 2024, https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/

HARVARD

Judith-Excel-Sharing | Sciencx Wednesday August 28, 2024 » #53 — Get Desired Data Every N Rows And Combine Them Into One Row., viewed ,<https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/>

VANCOUVER

Judith-Excel-Sharing | Sciencx - » #53 — Get Desired Data Every N Rows And Combine Them Into One Row. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/

CHICAGO

" » #53 — Get Desired Data Every N Rows And Combine Them Into One Row." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/

IEEE

" » #53 — Get Desired Data Every N Rows And Combine Them Into One Row." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/. [Accessed: ]

rf:citation

» #53 — Get Desired Data Every N Rows And Combine Them Into One Row | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2024/08/28/53-get-desired-data-every-n-rows-and-combine-them-into-one-row/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.