In Excel, Combine Every N Row into A New Row

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.

Comments

[Judith-Excel-Sharing]

SPL XLL always provides dynamic and customizable functions that are easy to operate! Come and join us to level up your Excel skills! scudata.com/download-Desktop

[Eckehard]

You can use the indirect function to calculate cell adresses

[Judith-Excel-Sharing]

The original task is: stackoverflow.com/questions/783440.... And the solutions offered by Excel are a bit inconvenient. While with SPL XLL, it can be much improved.

[Eckehard]

Oh, you are promoting a commercial product, so I understand your focus is a bit different. Maybe you should mention this.

I prefer to use the inbuild functions and - by the way - the solution you provide is pretty complicated, so maybe the best promotion.

[Judith-Excel-Sharing]

Haha, those who aren't familiar with SPL XLL might consider it complicated, but that's okay! Thank you for your feedback! Yes, esProc Desktop is a commercial product designed to enhance and simplify complex Excel tasks. I understand that some people might prefer using inbuilt functions, but esProc offers a powerful alternative for more intricate operations. While it may seem complicated at first glance, it significantly reduces the manual effort and complexity involved in these tasks. It's all about finding the right tool for the job! 😊