RaqForum 41 No.
178 View  • 
     ...

Excel Perform COUNT on each category and concatenate results into a string

 

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

Use SPL XLL to enter the following formula:

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

Picture1png

The conj()function concatenates subsets; 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.

Source:https://stackoverflow.com/questions/78364934/generate-a-string-of-combined-words-without-duplicates


 
 •  178 View  •