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:

r/esProc_Desktop - In Excel, Combine Every N Rows into A New Row

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.

SPL XLL is now FREE to download and apply: http://www.scudata.com/download-Desktop

In Excel, Combine Every N Row into A New Row

Subscribe to my newsletter

esProc Desktop

esProc Desktop