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

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:

Solution:

Use SPL XLL to enter the following formula:

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

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.

Comments

[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

[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/