mrt1
asked on
Identifying duplicate entries in a list
I have a field on a form which is a list of names, and need to check to see if there are any duplicates. I want to do this using a separate calculated field which will list any entries that appear more than once in the source field.
How do I do this using a formula. My best guess is to use the @Unique function and then compare this to the original list, but I'm not if this is possible.
Help would be appreciated!
How do I do this using a formula. My best guess is to use the @Unique function and then compare this to the original list, but I'm not if this is possible.
Help would be appreciated!
Or, if you really want to know, use
@If(@Elements(TheField)=@E lements(@U nique(TheF ield)); @Success; @Failure("..."))
@If(@Elements(TheField)=@E
Here is the formula to extract duplicates from a List
List := FIELD_NAME_HERE;
num := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
idList := @Subset( num*+ num; @Elements(List));
tmp1 := @Unique(@Replace(List; List; idList));
tmp2 := @Trim(@Replace(idList; tmp1; ""));
@Replace(tmp2; idList; List);
~Hemanth
List := FIELD_NAME_HERE;
num := "0" : "1" : "2" : "3" : "4" : "5" : "6" : "7" : "8" : "9";
idList := @Subset( num*+ num; @Elements(List));
tmp1 := @Unique(@Replace(List; List; idList));
tmp2 := @Trim(@Replace(idList; tmp1; ""));
@Replace(tmp2; idList; List);
~Hemanth
ASKER
My 'list' is actually a concatenation of several list fields - that is why I'm looking for duplicates. I need to return the duplicates themselves - not just a boolean value. This calculated field is going to be displayed on the form as a validation warning -so that users are made aware of the duplicates.
Isn't there a more elegant way of doing it that having to hard code a list of numbers ( There isn't an upper limit to the number of entries in the list, so I don't really want to do this).
I could probably do this using Lotus script, but I just thought it would be simpler using a formula.
Isn't there a more elegant way of doing it that having to hard code a list of numbers ( There isn't an upper limit to the number of entries in the list, so I don't really want to do this).
I could probably do this using Lotus script, but I just thought it would be simpler using a formula.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just put in a Translation formula, to get rid of the duplicates :
@unique(TheField)
cheers,
tom