#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 …


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

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:

original table
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:

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

result table with code entered

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


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-29T02:25:24+00:00) #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell. Retrieved from https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/

MLA
" » #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell." Judith-Excel-Sharing | Sciencx - Thursday August 29, 2024, https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/
HARVARD
Judith-Excel-Sharing | Sciencx Thursday August 29, 2024 » #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell., viewed ,<https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/>
VANCOUVER
Judith-Excel-Sharing | Sciencx - » #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/
CHICAGO
" » #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/
IEEE
" » #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/. [Accessed: ]
rf:citation
» #54 - Combine Cell Values of Every 3 Rows under Each Column into One Cell | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2024/08/29/54-combine-cell-values-of-every-3-rows-under-each-column-into-one-cell/ |

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.

Related Posts