RaqForum 41 No.
155 View  • 
     ...

Combine cell values of every 3 rows under each column into one cell

 

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:

A

B

C

D

E

1

Fruit

Breed

Shop

price

qty

2

Musk


3

Lime

Lavonne

1

4

4

Melon


5

Apple

Kirin

Way Side

2

5

6




7




8

peach

Japan


9

Kubo


10

Daimomo

Cshop

3

4

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:

G

H

I

J

K

1

Fruit

Breed

Shop

price

qty

2

Lime

Musk Melon

Lavonne

1

4

3

Apple

Kirin

Way Side

2

5

4

peach

Japan Kubo Daimomo

Cshop

3

4

Enter the following formula in SPL XLL:

=spl("=E@2p(E@2p(?).(~.group((#-1)\3).(~.select(~).concat(""""))))",A2:E10)

Picture1png

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

Source:https://www.reddit.com/r/excel/comments/1cfwjty/combine_3_rows_into_a_single_row_but_keep_the/


 
 •  155 View  •