Comparing TEXT values

YS_*12825

Hi,

Very simple task from the looks of it.

I have a line item where user (or formula, I don't think it matters) enters text. I want this text be unique, and if it isn't - I want user to know about it. How do I make a check?

 

If it were cells formatted as list members - I can think of a way to compare those, but many functions do not work with text fields.

Any ideas are appreciated.

cianna.bramley

Hey!

 

I've included 2 screenshots here of different ways you could do this - whether you'd prefer a written text error or more a more visual "red box" error. (You can see the formula for each in the top of the screenshot - and the second also uses conditional formatting to highlight the "red" cells, alternatively you can use a boolean when checking if it's unique, instead of 1's and 0's!)

One thing to note with this solution is that it won't bring back an error for the first occurence of the text (unlike in your screenshot), only the repeated text inputs. I'm not sure if you'd need it for the first occurrence too?

Hope this helps!

Cianna

LipChean_Soh

Hi,

 

Please refer to the following:

I've also created 2 lists, i.e.

1. Transaction A - Corresponds to your line 1, line 2, line 3, etc

2. Agg Text - This is more of a dummy list, with only 1 member, called 1 Mbr

 

I've created 2 modules, i.e.

1.Unique Text Transaction: This module uses list "Transaction A" and "Agg Text"

2. Combine All Text: This module uses list "Agg Text"

 

For the "Unique Text Transaction" module, you have the following line item:

1. Text Input (format: Text) - This is where you have your text to be tested for uniqueness,

2. Text Input Length (format: Number) - To calculate the length of "Text Input", formula = LENGTH(Text Input)

3. Replace Text (format: Text) - This looks for 'Text Input' within the 'Combine All Text' below, and replace with blank, formula = SUBSTITUTE(Combine All Text.Value, Text Input, "")

4. Replaced Length (format: number) - This calculates the length of 'Replace Text', formula = LENGTH(Replace Text)

5. Replaced Count (format: number) - This calculates the instances of 'Text Input' replaced, formula = (Combine All Text.Original Length - Replaced Length) / Text Input Length. Note that unique instance will only be replaced ONCE.

6. Error message (format: text) - This shows the relevant error message or not, formula = IF Replaced Count > 1 THEN "Not Unique!" ELSE ""

 

For the "Combine All Text" module, you have the following line items:

1. Value (format: Text) - This is where you combine all text input from "Unique Text Transaction", formula = TEXTLIST(Unique Text Transaction.Text Input, ",", Transaction A, ALL)

2. Original Length (format: number) - This calculates the length of Value, formula = LENGTH(Value)

 

Obviously you can optimize the formulas a bit, but i just want to expand everything for easier understanding.

 

Thanks,

LipChean

jjwikselaar

Not a very simple solution maybe... but you could create an action that creates a list from all the 'Text inputs' and then to the check based on the list. 

The disadvantage is that you need to run an action / process before the check is applied.

 

Can't think of an easier way to do this.... if something better comes in my mind I let you know 🙂

YS_*12825

Thank you for your input. From user interface perspective running a process to verify if this same user entered non-unique text seems a bit too heavy. I'd like a more elegant solution, if possible.

YS_*12825

You are right, first occurance is ok even if not unique, so your solution is both elegant and functional. I have not come across this function before, but I can see it was designed for cases like this. Thank you!

YS_*12825

This is a very interesting approach as well. Not so short as Cianna's, but it has added value of getting all non-unique lines, including the first one. It is not very important in this particular case, but may be important in some other, similar case. I learned new things from it, thank you!

Egor

Hi LipChean,

This is a great solution, that unlike ISFIRSTOCCURRENCE formula marks all cases of non-unique record, including the very first one.

Thank you for this!

Best regards,

Egor.

DavidSmith

Just one word of caution on Textlist.  Be careful if this is over a large list.  As per Duncan Pearson's AMA sesison, text (and text strings) take up a lot of memory, so we should look to minimise their use if possible

 

Duncan's session is here if you haven't seen it

https://community.anaplan.com/t5/Up-Next-Archives/Duncan-Pearson-Designing-for-Performance-and-Scale/gpm-p/36431

 

David