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.
SPL XLL is now FREE to download and apply: http://www.scudata.com/download-Desktop