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?
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.
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!
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
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!