RemoveDuplicates ( Text )
Removes duplicate values from a value list.
Average rating: 3.8 (52 votes) Log in to vote
|
Andrew Persons - Show more from this author
Excelisys http://www.excelisys.com |
Sample input:
One
One
One
one
Two
one
two
Two
One
Three
Two
One
Two
Three
One
One
one
Two
one
two
Two
One
Three
Two
One
Two
Three
Sample output:
One
one
Two
two
Three
one
Two
two
Three
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
This function is case sensitive, but the values do not need to be alphabetized, and the duplicates do not need to be next to each other. It will leave the list in the order it received it, minus any duplicates.
Comments
 |
Daniel A. Shockley, New York, NY Nov 16, 2010 |
|
| Important to note: This script wipes out any "pipe" ( "|" )characters from your list. Side effects like that are important to know about. | ||
|
Patrick Neame, Ironbridge Aug 10, 2012 |
|
| What in the RemoveDuplicates function and why can it not be found? | ||
|
Joshua, New Jersey Jun 6, 2013 |
|
| I like that it removes the duplicates in a vlaue list but where do i insert this code at to remove my duplicates from my value list? Also, which fields get renamed from your script? |
||
 |
Daniel Sep 20, 2013 |
|
| This is awesome. Works really well when creating lists from multiple fields. Thank you. | ||
|
Christopher Bailey, Boston, MA Sep 29, 2014 |
|
| Instead of using the | character in the function (which might very well be used in a list element), I use a random sequence of characters that nobody would ever type. to wit: //================================== Let ( [ TheValue = LeftValues ( Text ; 1 ) ; TheCount = ValueCount ( Text ) ; NewText = Replace ( Substitute ( "¶" & textoo & "¶" ; [ "¶" ; "¶%%%UUU%%%" ] ; [ "%%%UUU%%%" & TheValue ; "" ] ; [ "%%%UUU%%%" ; "" ] ) ; 1 ; 1 ; "" ) ] ; Case ( TheCount > 0 ; TheValue & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) ) ) ) // // // In the unlikely event that any actual data contains %%%UUU%%%, you may need to tweak this function :) // // //=================================== |
||
|
Jon, Grande Prairie Feb 27, 2015 |
|
| Why do you even need the pipe bar or any other string for substitution? It works great by just using carriage returns. unless im missing something... | ||
|
Jon, Grande Prairie Feb 27, 2015 |
|
| Let ( [ TheValue = GetValue ( Text ; 1 ) ; NewText = Replace ( Substitute ( ¶ & Text & ¶ ; ¶ & TheValue ; "" ) ; 1 ; 1 ; "" ); TheCount = ValueCount ( NewText ) ] ; TheValue & If( TheCount > 0 ; ¶ & RemoveDuplicates ( Replace ( NewText ; Length ( NewText ) ; 1 ; "" ) ) /*Stop*/ ) ) // // // This Allows you to not worry about any character that might be removed through this Function // // PLUS the Stop section allows you to insert any suffix you prefer, I like my list to return with no carriage return at the end so this works great for me. // |
||
|
Jon, Grande Prairie Feb 27, 2015 |
|
| nvm, my function removes "the" from "theater".. I forgot that leftValues puts a carriage return at the end. But the order it evaluates is still usable for preventing a pesky carriage return at the end of your list | ||
 |
James, Northwest Enforcement Feb 18, 2016 |
|
| This is great, did exactly what I needed | ||
|
franck, Be Jan 4, 2017 |
|
| is it possible to remove duplicate plural ? for exemple "toy game toys brand" so it wll done "toy game brand" ? |
||
|
Andy Persons, Sierra Vista, AZ Jan 4, 2017 |
|
| That's possible, but it would require some extra work. Also, certain plurals would be more problematic, such as jelly/jellies. | ||
|
franck, Be Jan 4, 2017 |
|
| could you help me to make it work ? bad luck for jelly ))) i m trying since few hours with this "If ( Right ( text ; 1 )="s" ; Left ( text ; Length ( text )-1 ) ; text )" |
||
|
Andy Persons, Sierra Vista, AZ Jan 4, 2017 |
|
| After giving it more thought, I'm not sure how well it would work in practice. You'd have to do multiple passes to remove plurals that were encountered before their singulars, among other issues. Unfortunately, this will take more time than I can spend pro bono. Good luck! |
||
|
Scott, UK Jun 5, 2017 |
|
| Could this be adjusted to remove duplicate strings of text as opposed to just a single word? | ||
|
Aaron, Wooster, OH, USA Jun 8, 2017 |
|
| Scott, I think it does that already. Did you test it? The parameter the function requires is a string; and it looks for identical strings. | ||
 |
Witschi, Medio-Ingeno AG Sep 15, 2022 |
|
| Consider as well the native function UniqueValues ( values {; datatype ; locale } ) | ||
 |
PhilFileMaker Sep 2, 2023 |
|
| A nice function would be to SHOW what values are duplicate | ||
|
PhilFileMaker Sep 2, 2023 |
|
| A nice function would be to SHOW what values are duplicate If ListA is C_001 C_001 C_002 and ListB is C_001 C_002 We should receive as result C_001 |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.