In Excel, Combine Every N Row into A New Row

Judith-Excel-Sharing - Jun 6 -

- Dev Community

Problem description & analysis:

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

Solution:

Use SPL XLL to enter the formula below:

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

As shown in the picture below:

Explanation:

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