+ Start a Discussion
BdoggBdogg 

Concatenate Formula deleting duplicate values.

I am trying to do a concatenate merging two text fields.  Occasioanlly the concatenated field may contain a duplicate value. I am looking for a way to delete the duplicate value so only the value only appears once. 

 

IE: Field 1: 123567,88595

      Field 2: 987987,88595

 

Concatenate Field Result: 123567,88595, 987987,88595

 

Desired Result: 123567,987987,88595

 

Can anyone suggest a way to do this? 

rbohnrbohn
If there's a limited number of known text elements you can dedup by using an
IF statement and a FIND statement. Test for each unique case and build the
string you need.



If there's not a limited number of text elements --- I suspect you'd have to
write an APEX trigger to do.
Naveen NelavelliNaveen Nelavelli

If your field is going to have only two values then try like this

 

1.separate field1 value using "," ,compare two values with field2  values and try to remove duplicate.

 

 

if(CONTAINS(LEFT(field1,FIND(field1,",",1),field2)),
 Right(field1,FIND(field1,",",1)+field2,
    if(
       CONTAINS(right(field1,FIND(field1,",",1),field2)),
                    LEFT(field1,FIND(field1,",",1)+field2),
                   field2+field1
      )
)

 

 

** if it is givinng error for "," ....use substitute to replace ","with  *.. like  

 

if(

    CONTAINS(

                      LEFT(

                                 field1,FIND(SUBSTITUTE(field1,"',",*),*,1),field2

                       )

)

 

 

 

 

 

** this can be done easily using apex..just let me know if your interested to do using apex..

BdoggBdogg

HI Naveen, 

 

Unfortunatly its not always going to have only two values, so this option won't work either.  I appreciate the idea.  I'd be interested in doing it with APEX, however I am not firmiliar at all with APEX.  If you can help me with it, I'd appreciate the help.

Thanks!

 

-Brandon


Naveen Nelavelli wrote:

If your field is going to have only two values then try like this

 

1.separate field1 value using "," ,compare two values with field2  values and try to remove duplicate.

 

 

if(CONTAINS(LEFT(field1,FIND(field1,",",1),field2)),
 Right(field1,FIND(field1,",",1)+field2,
    if(
       CONTAINS(right(field1,FIND(field1,",",1),field2)),
                    LEFT(field1,FIND(field1,",",1)+field2),
                   field2+field1
      )
)

 

 

** if it is givinng error for "," ....use substitute to replace ","with  *.. like  

 

if(

    CONTAINS(

                      LEFT(

                                 field1,FIND(SUBSTITUTE(field1,"',",*),*,1),field2

                       )

)

 

 

 

 

 

** this can be done easily using apex..just let me know if your interested to do using apex..


 

Naveen NelavelliNaveen Nelavelli

 

 

 

 

public string removeDuplicate(){

   Set<String> set1=new set<string>();

  String str=' ';

    if(field1!=null && field 1!=' ''){

    

       set1.addall(field1.split(','));

//splitting strings using ' , '

//adding them to sets will remove dupicate values

    }

 

    if(field2!=null && field 21!=' ''){

 

           set1.addall(field2.split(','));

     }

 

for(string sr:set1){

//iterating set and concatinating values

 

   Str+=sr;

}

 

return trim(str);

// this wil be your final string

}

 

 

 

** i didnt do practically but this is solution,let me know any issues

BdoggBdogg

Thank you, as I mentioned I have never worked with Apex Code.  Can you tell me how to use this code?