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.
Top comments (6)
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
You can use the indirect function to calculate cell adresses
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.
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.
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! 😊
Some comments may only be visible to logged-in visitors. Sign in to view all comments.