DEV Community

Cover image for Excel Perform COUNT on Each Category and Concatenate Results into a String
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

Excel Perform COUNT on Each Category and Concatenate Results into a String

Problem description & analysis:

In the following Excel table, there are duplicate values in column A:

    A
1   Fruit
2   Apple
3   Banana
4   Banana
5   Strawberry
Enter fullscreen mode Exit fullscreen mode

Computing task: perform COUNT on each category and concatenate result groups into a string with "+"; if the count is greater than 1, write "x count" after each category. The final result will be like this:

Apple+Bananax2+Strawberry

As shown in the picture below:

Table with code entered

Solution:

Use SPL XLL to enter the following formula:

=spl("=?.conj().groups(~;count(1)).(#1 / if(#2>1,$[x] / #2)).concat($[+])",A2:A5)
Enter fullscreen mode Exit fullscreen mode

Explanation:

The conj()function concatenates subsets; the groups() function performs grouping & aggregation; with $[], we do not need to escape a string with double quotation marks, and #1 represents the 1st field of the table.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Do you find the solution effective or have any thoughts? Please do not hestiate to share them. We are more than happy to hear! If you find this Excel case interesting, please try this solution by simply clicking this link: scudata.com/download-Desktop

Collapse
 
judith677 profile image
Judith-Excel-Sharing

We also have many Excel experts in our Reddit community where you can ask away any Excel problems! reddit.com/r/esProc_Desktop/