PranavChoudhary
Qrew Trainee
3 years ago

# De-Duplicate Data in a Text Field.

I am bringing data into a Formula-Text field from another table using formula query function.
Now, data has entries separated by ";" and I want only distinct entries to be displayed. For Example : ABC;DEF;DEF;GHI;DEF;ABC are the entries and I want only ABC;DEF;GHI as output.
My data has more than 20 entries so bringing it in as multi-select text doesn't fits.
If I Split the entries on basis of ; and collects them in Multi-Select Text and use summary field to display it in parent table then problem is solved but then again Multi-Select Text allows only 20 entries and I have more than that so Summary field only summarise from those 20.
------------------------------
Pranav Choudhary
------------------------------
• The only method I know if brute force.  I think that this example from my cheat note was to turn it into a vertical list but you could probably adapt this formula to not have the breaks to a new line  \n.

If you do decide to tackle this as a longer brute force formula can you post your final solution?

var text RawList = [My semi delimited text field];

var text A = Trim(Part(\$RawList,1,";"));

var text B = If(not Contains(\$A,Trim(Part(\$RawList,2,";"))), List("\n", \$A, Trim(Part(\$RawList,2,";"))), \$A);

var text C = If(not Contains(\$B,Trim(Part(\$RawList,3,";"))), List("\n", \$B, Trim(Part(\$RawList,3,";"))), \$B);

var text D = If(not Contains(\$C,Trim(Part(\$RawList,4,";"))), List("\n", \$C, Trim(Part(\$RawList,4,";"))), \$C);

var text E = If(not Contains(\$D,Trim(Part(\$RawList,5,";"))), List("\n", \$D, Trim(Part(\$RawList,5,";"))), \$D);

var text F = If(not Contains(\$E,Trim(Part(\$RawList,6,";"))), List("\n", \$E, Trim(Part(\$RawList,6,";"))), \$E);

var text G = If(not Contains(\$F,Trim(Part(\$RawList,7,";"))), List("\n", \$F, Trim(Part(\$RawList,7,";"))), \$F);

var text H = If(not Contains(\$G,Trim(Part(\$RawList,8,";"))), List("\n", \$G, Trim(Part(\$RawList,8,";"))), \$G);

var text I = If(not Contains(\$H,Trim(Part(\$RawList,9,";"))), List("\n", \$H, Trim(Part(\$RawList,9,";"))), \$H);

var text J = If(not Contains(\$I,Trim(Part(\$RawList,10,";"))), List("\n", \$I, Trim(Part(\$RawList,10,";"))), \$I);

var text K = If(not Contains(\$J,Trim(Part(\$RawList,11,";"))), List("\n", \$J, Trim(Part(\$RawList,11,";"))), \$J);

var text L = If(not Contains(\$K,Trim(Part(\$RawList,12,";"))), List("\n", \$K, Trim(Part(\$RawList,12,";"))), \$K);

var text M = If(not Contains(\$L,Trim(Part(\$RawList,13,";"))), List("\n", \$L, Trim(Part(\$RawList,13,";"))), \$L);

var text N = If(not Contains(\$M,Trim(Part(\$RawList,14,";"))), List("\n", \$M, Trim(Part(\$RawList,14,";"))), \$M);

var text O = If(not Contains(\$N,Trim(Part(\$RawList,15,";"))), List("\n", \$N, Trim(Part(\$RawList,15,";"))), \$N);

var text P = If(not Contains(\$O,Trim(Part(\$RawList,16,";"))), List("\n", \$O, Trim(Part(\$RawList,16,";"))), \$O);

var text Q = If(not Contains(\$P,Trim(Part(\$RawList,17,";"))), List("\n", \$P, Trim(Part(\$RawList,17,";"))), \$P);

var text R = If(not Contains(\$Q,Trim(Part(\$RawList,18,";"))), List("\n", \$Q, Trim(Part(\$RawList,18, ";"))), \$Q);

var text S = If(not Contains(\$R,Trim(Part(\$RawList,19,";"))), List("\n", \$R, Trim(Part(\$RawList,19,";"))), \$R);

var text T = If(not Contains(\$S,Trim(Part(\$RawList,20,";"))), List("\n", \$S, Trim(Part(\$RawList,20,";"))), \$S);

var text U = If(not Contains(\$T,Trim(Part(\$RawList,21,";"))), List("\n", \$T, Trim(Part(\$RawList,21,";"))), \$T);

var text V = If(not Contains(\$U,Trim(Part(\$RawList,22,";"))), List("\n", \$U, Trim(Part(\$RawList,22,";"))), \$U);

var text W = If(not Contains(\$V,Trim(Part(\$RawList,23,";"))), List("\n", \$V, Trim(Part(\$RawList,23,";"))), \$V);

var text X = If(not Contains(\$W,Trim(Part(\$RawList,24,";"))), List("\n", \$W, Trim(Part(\$RawList,24,";"))), \$W);

var text Y = If(not Contains(\$X,Trim(Part(\$RawList,25,";"))), List("\n", \$X, Trim(Part(\$RawList,25,";"))), \$X);

var text Z = If(not Contains(\$Y,Trim(Part(\$RawList,26,";"))), List("\n", \$Y, Trim(Part(\$RawList,26,";"))), \$Y);

\$Z

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

### 2 Replies

• The only method I know if brute force.  I think that this example from my cheat note was to turn it into a vertical list but you could probably adapt this formula to not have the breaks to a new line  \n.

If you do decide to tackle this as a longer brute force formula can you post your final solution?

var text RawList = [My semi delimited text field];

var text A = Trim(Part(\$RawList,1,";"));

var text B = If(not Contains(\$A,Trim(Part(\$RawList,2,";"))), List("\n", \$A, Trim(Part(\$RawList,2,";"))), \$A);

var text C = If(not Contains(\$B,Trim(Part(\$RawList,3,";"))), List("\n", \$B, Trim(Part(\$RawList,3,";"))), \$B);

var text D = If(not Contains(\$C,Trim(Part(\$RawList,4,";"))), List("\n", \$C, Trim(Part(\$RawList,4,";"))), \$C);

var text E = If(not Contains(\$D,Trim(Part(\$RawList,5,";"))), List("\n", \$D, Trim(Part(\$RawList,5,";"))), \$D);

var text F = If(not Contains(\$E,Trim(Part(\$RawList,6,";"))), List("\n", \$E, Trim(Part(\$RawList,6,";"))), \$E);

var text G = If(not Contains(\$F,Trim(Part(\$RawList,7,";"))), List("\n", \$F, Trim(Part(\$RawList,7,";"))), \$F);

var text H = If(not Contains(\$G,Trim(Part(\$RawList,8,";"))), List("\n", \$G, Trim(Part(\$RawList,8,";"))), \$G);

var text I = If(not Contains(\$H,Trim(Part(\$RawList,9,";"))), List("\n", \$H, Trim(Part(\$RawList,9,";"))), \$H);

var text J = If(not Contains(\$I,Trim(Part(\$RawList,10,";"))), List("\n", \$I, Trim(Part(\$RawList,10,";"))), \$I);

var text K = If(not Contains(\$J,Trim(Part(\$RawList,11,";"))), List("\n", \$J, Trim(Part(\$RawList,11,";"))), \$J);

var text L = If(not Contains(\$K,Trim(Part(\$RawList,12,";"))), List("\n", \$K, Trim(Part(\$RawList,12,";"))), \$K);

var text M = If(not Contains(\$L,Trim(Part(\$RawList,13,";"))), List("\n", \$L, Trim(Part(\$RawList,13,";"))), \$L);

var text N = If(not Contains(\$M,Trim(Part(\$RawList,14,";"))), List("\n", \$M, Trim(Part(\$RawList,14,";"))), \$M);

var text O = If(not Contains(\$N,Trim(Part(\$RawList,15,";"))), List("\n", \$N, Trim(Part(\$RawList,15,";"))), \$N);

var text P = If(not Contains(\$O,Trim(Part(\$RawList,16,";"))), List("\n", \$O, Trim(Part(\$RawList,16,";"))), \$O);

var text Q = If(not Contains(\$P,Trim(Part(\$RawList,17,";"))), List("\n", \$P, Trim(Part(\$RawList,17,";"))), \$P);

var text R = If(not Contains(\$Q,Trim(Part(\$RawList,18,";"))), List("\n", \$Q, Trim(Part(\$RawList,18, ";"))), \$Q);

var text S = If(not Contains(\$R,Trim(Part(\$RawList,19,";"))), List("\n", \$R, Trim(Part(\$RawList,19,";"))), \$R);

var text T = If(not Contains(\$S,Trim(Part(\$RawList,20,";"))), List("\n", \$S, Trim(Part(\$RawList,20,";"))), \$S);

var text U = If(not Contains(\$T,Trim(Part(\$RawList,21,";"))), List("\n", \$T, Trim(Part(\$RawList,21,";"))), \$T);

var text V = If(not Contains(\$U,Trim(Part(\$RawList,22,";"))), List("\n", \$U, Trim(Part(\$RawList,22,";"))), \$U);

var text W = If(not Contains(\$V,Trim(Part(\$RawList,23,";"))), List("\n", \$V, Trim(Part(\$RawList,23,";"))), \$V);

var text X = If(not Contains(\$W,Trim(Part(\$RawList,24,";"))), List("\n", \$W, Trim(Part(\$RawList,24,";"))), \$W);

var text Y = If(not Contains(\$X,Trim(Part(\$RawList,25,";"))), List("\n", \$X, Trim(Part(\$RawList,25,";"))), \$X);

var text Z = If(not Contains(\$Y,Trim(Part(\$RawList,26,";"))), List("\n", \$Y, Trim(Part(\$RawList,26,";"))), \$Y);

\$Z

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
• Thanks Mark. It was really helpful.

------------------------------
Pranav Choudhary
------------------------------