Counting unique elements in a text list
This tip's formula will count the number of elements that are the same in a text list.
The following formula will count the number of elements that are the same in a text list.
List1 := @Explode ( "Red,Blue,Red, Green,Red,Blue"; "," ) ; List2 := List1 + "@" ; List3 := @Unique ( List1 ) ; List4 := @ReplaceSubstring ( List3; List1; List2 ) ; List5 := @ReplaceSubstring ( List4; List3; "" ) ; @Length ( List5 )
In this example it returns 3; 2; 1. That is, there are 3 occurrences of red, 2 blue and 1 green.
How it works
List1 can be any text list field or variable.
List2 is all the elements of List1 with @ appended to the end. You can use other characters. So, in this example, List2 looks like:
[email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]
List3 is a text list of the unique values within List1:
Red; Blue; Green
List4 replaces the values in List3 with those in List2, which results in an @ sign for each occurrence:
[email protected]@@; [email protected]@; [email protected]
List5 removes the List3 values from List4 and returns:
@@@; @@; @
The last command now returns the length of each item in the list:
3; 2; 1
With a bit more list manipulation you can return:
Red 3
Blue 2
Green 1
List1 := @Explode ( "Red,Blue,Red, Green,Red,Blue"; "," ) ; List2 := List1 + "@" ; List3 := @Unique ( List1 ) ; List4 := @ReplaceSubstring ( List3; List1; List2 ) ; List5 := @ReplaceSubstring ( List4; List3; "" ) ; @Length ( List5 )
Do you have comments on this tip? Let us know.
