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
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.
