Discussions

View Only

Check to see if a value in one list, is contained in another list

• 1.  Check to see if a value in one list, is contained in another list

Posted 18 days ago
How would I search through one list and see if any of those values are contained in another list?

Then return it as a formula checkbox, or formula text, the output doesn't matter I can deal with that. Just not how to approach this.

------------------------------
Mike Tamoush
------------------------------

• 2.  RE: Check to see if a value in one list, is contained in another list

Posted 18 days ago
so two field son the same record.

like

cats;dogs;parrots

and the other list is
fish;hamster;cats

so that would be TRUE because they both contain cats?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

• 3.  RE: Check to see if a value in one list, is contained in another list

Posted 18 days ago
Correct. It's TRUE if ANY of the words in list 1, is contained in list 2.

In my case it's two lists of dates.

------------------------------
Mike Tamoush
------------------------------

• 4.  RE: Check to see if a value in one list, is contained in another list

Posted 18 days ago
Edited by Mike Tamoush 18 days ago
Bonus points if you can return every duplicate in a text list. So instead of returning true, return a list of each item in list 1 that is contained in list 2.

My gut says I need to use the Part function and just manually create a formula with the max number of items I believe will be in list 1.
------------------------------
Mike Tamoush
------------------------------

• 5.  RE: Check to see if a value in one list, is contained in another list

Posted 16 days ago
Mike
Here is an example using Multi select fields for the input and output. The result will be a multi select apparance with the unique values.

var text ListOne = ToText([List 1]);
var text ListTwo = ToText([List 2]);

var text A = Trim(Part(\$ListOne,1,";"));
var text B = Trim(Part(\$ListOne,2,";"));
var text C = Trim(Part(\$ListOne,3,";"));
var text D = Trim(Part(\$ListOne,4,";"));
var text E = Trim(Part(\$ListOne,5,";"));

Split(List(";",
If(Contains(\$ListTwo,\$A), \$A),
If(Contains(\$ListTwo,\$B), \$B),
If(Contains(\$ListTwo,\$C), \$C),
If(Contains(\$ListTwo,\$D), \$D),
If(Contains(\$ListTwo,\$E), \$E)))

.........

and here is the count of the # of common terms

var text String = ToText([List 2 in List 1]);

Count(
Trim(Part(\$String,1, ";"))<>"",
Trim(Part(\$String,2, ";"))<>"",
Trim(Part(\$String,3, ";"))<>"",
Trim(Part(\$String,4, ";"))<>"",
Trim(Part(\$String,5, ";"))<>"")

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider