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.
Top comments (2)
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
We also have many Excel experts in our Reddit community where you can ask away any Excel problems! reddit.com/r/esProc_Desktop/