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.