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

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
Enter fullscreen mode
Exit fullscreen mode

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 
Enter fullscreen mode
Exit fullscreen mode

Solution:

Use SPL XLL to enter the following formula:

=spl("=E(?).group@o(#2).(#1|#2|~.(#3))",A1:C8)
Enter fullscreen mode
Exit fullscreen mode

As shown in the picture below:

result table with code entered

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.


Discover more from Coursity

Subscribe to get the latest posts to your email.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *