#54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell

Problem description & analysis:
The Excel table below has a nonstandard format - every 3 rows correspond to one record and under each row title, every 3 rows correspond to one column, as shown below:

Task: Organize the table as a standard one by combining cell values of every 3 rows under each column into one cell and delimit them with spaces. Below is the expected result:

Solution:
Enter the following formula in SPL XLL:
=spl("=E@2p(E@2p(?).(~.group((#-1)\3).(~.select(~).concat(""""))))",A2:E10)
As shown in the picture below:

Explanation:
E@2p transposes the table. group() groups rows; # is the row number.

For more information:
⭐️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

⭐️YouTube FREE course: SPL Programming

Comments

[Judith-Excel-Sharing]

Please feel free to reach out if you have any Exel problems. We'll be more than happy to help:

✨Discord: discord.gg/PVyKVa2J
✨Reddit: reddit.com/r/esProc_Desktop/