Compare TEXT Values in Anaplan
Technical Summary Statement
The objective of this article is to compare values in a TEXT formatted line item and identify the values that occur more than once (duplicates).
Sneakerz is an up-and-coming shoe company with a variety of shoe brands in its brand portfolio. They are expanding internationally after having started from their humble origins in countryside Australia. As part of their international strategy, the marketing team is coming up with new ideas for brand names.
Sneakerz uses Anaplan for Connected Planning across the organization. The marketing team wants to enter the new brand names in Anaplan and expects Anaplan to highlight the duplicates.
For example, if ‘Comfort’ is entered on two separate rows, Anaplan should highlight the second one as a duplicate.
Final solution should look similar to the following:
- Create an input module “INP01 – Brand Names” that allows a user to enter the New Brand Name as an input.
- Create a new line item ‘Valid Name?’. This will check that the New Brand Name is unique across the numbered list (and highlight if there is more than one occurrence). The formula for the line item is as follows:
Note: The formula above ignores the blanks (if no input is entered). If you want to use conditional formatting to highlight the duplicate texts, then continue reading.
- Add a line item ‘Cond Format | Not Unique?’ to indicate second or more occurrences in ‘Valid Name?’ line item using conditional. Ensure that the Summary Method is set to ‘None’.
- Go out of the Blueprint view. The module should look like this:
- Optional: Use the Conditional Formatting feature to highlight the duplicate occurrences in the New Brand Name field.
- The final output is shown below:
The final output view indicates that there are three New Brand Names that are duplicates namely – Comfort, Ethical, Futura. On the other hand, two New Brand Names – Runner and Bambino are unique in the data set.
Pitfalls of this Approach
The limitation with this solution is that you can only identify the successive duplicates and cannot tag the first occurrence as a duplicate value too.
- Always follow best practices and turn off summary calculations if not necessary.
- Rename and organize modules in relevant functional areas.
- Avoid Daisy Chaining in the calculations if they can run in parallel.
Contributing author Rob Marshall.