...

In Excel, combine every N rows into a new row

 

In column F, every four rows correspond to one record:

A

B

C

D

E

F

1

Name

Address

City

Short ID

Company 1

2




2222 al street

3




Blue cheese

4




1

5




Company 2

6




1111 arm rd

7




Ranch

8




2

9




Company 3

10




3333 raindrop drive

11




Peanut

12




3

We need to re-arrange column F to make a standard table by entering each record to cells A~D row by row:

A

B

C

D

E

F

1

Name

Address

City

Short ID

Company 1

2

Company 1

2222 al street

Blue cheese

1

2222 al street

3

Company 2

1111 arm rd

Ranch

2

Blue cheese

4

Company 3

3333 raindrop drive

Peanut

3

1

5




Company 2

6




1111 arm rd

7




Ranch

8




2

9




Company 3

10




3333 raindrop drive

11




Peanut

12




3

Use SPL XLL to enter the formula below:

=spl("=?.(~(1)).group((#-1)\4)",F1:F12)

Picture1png

~(1) represents getting the first sub-member of the current member. The group()function performs grouping operation by putting members having same (#-1)\4 to the same group; # represents ordinal number of a member, and symbol \ means a rounded division.

Source:https://stackoverflow.com/questions/78344042/i-have-a-list-of-data-in-column-f-that-i-want-to-move-into-columns-a-d


 
 •  152 View  •