In Excel, Enter Values of the same Category in Cells on the Right of the Grouping Cell in Order

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.