How to take the count of a text value that repeats

Certified Master Anaplanner

Re: How to take the count of a text value that repeats

The option above works but like you said there are many line items that factor into the formula.

The method below is for finding duplicate text fields using the aggregate x[TEXTLIST]y 

Conditions that apply, the list is part of a hierarchy and a parent will be used to aggregate the text fields using: (Text.Line Item[TEXTLIST: Parent.Line Item[Lookup: Systems List Parent Line Item])


1) The S | Systems modules line item to satisfy the L2 - L1 relationship for a Parent Lookup function.

2) Text String, or the series of text fields you wish to find duplicates of, in this case, there are two text fields separated by a delimiter "_" to separate the text string combinations.

3) This line item is Applied to the Parent of the L2 List applied to the Module. This formula uses the  x[TEXTLIST]y aggregate function to aggregate all of the L2 List text fields, input in the Text String Line Item (applied to L2) to its parent L1, separating the Text Strings with commas ( Text 1a_Text 2a , Text 1a_Text 2b, Text 1a, Text 2a 

4) Line Item uses the FIND function to find the first instance of the Text String Aggregated to the L1 Parent, by looking up the TEXTLIST AGGREGATOR using the L1 systems line item, starting at point 0 of the text field.

5) This line item is used to find the SECOND instance of a text field or a single duplicate, by making the starting point of the FIND function, the number in Line item 4, + 1 . This means that if the first instance of the string is found in line item 4 that number position + 1 will be the starting point of the second formula, and if the formula is larger than 0, then there is a duplicate. 

6) This is the BOOLEAN logic that indicates if a SINGLE DUPLICATE, or "Second Instance" of a text string is found.


This is not ideal for the "COUNT" of strings, as a line item will be needed to find each instance after the first duplicate, however, it is extremely powerful when creating items into a numbered lists and the names need to be unique and for unicity checks for unique code creation. 


The TEXTLIST function is not the fastest performing function, as it concatenates text fields which are 8 bytes of memory, and is limited to 10,000 items in a list. I would suggest only using this for hierarchies with less than 1,000 items for performance purposes. 


I hope this helps!