Problem description & analysis:
In the following Excel table, the 2nd column contains categories and the 3rd column contains detailed data:
A B C
1 S.no Account Product
2 1 AAAQ atAAG
3 2 BAAQ bIAAW
4 3 BAAQ kJAAW
5 4 CAAQ aAAP
6 5 DAAQ aAAX
7 6 DAAQ bAAX
8 7 DAAQ cAAX
We need to enter values in the same category in cells on the right of the grouping cell in order:
A B C D
1 S.no Account Product
2 1 AAAQ atAAG
3 2 BAAQ bIAAW kJAAW
4 4 CAAQ aAAP
5 5 DAAQ aAAX bAAX cAAX
Solution:
Use SPL XLL to enter the following formula:
=spl("=E(?).group@o(#2).(#1|#2|~.(#3))",A1:C8)
As shown in the picture below:
Explanation:
The E() function reads data in a stretch of cells as a table. group@o does not sort data before grouping. #1 is a simplified form and represents the 1st column of the 1st member in a group, and ~.(#3) means a sequence made up of values of the 3rd column of a member in a group.