Forum Discussion

MichaelTamoush's avatar
MichaelTamoush
Qrew Captain
5 years ago

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

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
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years 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
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MichaelTamoush's avatar
      MichaelTamoush
      Qrew Captain
      5 years 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
      ------------------------------
      • MichaelTamoush's avatar
        MichaelTamoush
        Qrew Captain
        5 years 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
        ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years 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(
      );
      var text ListTwo = ToText(
        );

        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(
          );

          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
          Your Quick Base Coach
          http://QuickBaseCoach.com
          mark.shnier@gmail.com
          ------------------------------