How to Compare lists of values (Sheets)

Generic809 0
Viewed 42

I am looking to compare cells containing a list of comma separated strings to a column containing solutions made up of comma-separated lists.

My test data

Each cell in the "Test lists" column will need to be checked against every one of the cells in the "Possible Solutions" column. If any exist that contain all of the values of the list we are currently testing, it should return TRUE. Otherwise, FALSE. It is OK if the possible solution contains MORE values, just not less.

The image above contains the best solution to this I have managed to work out so far ... the "Should be" column shows the answer I am expecting.

Any suggestions on other things I might try?

string-comparison google-sheets
1 Answers

This should work. Paste in cell D2 and drag!

=ARRAYFORMULA(SUM(N(ARRAYFORMULA( 
    LEN(FILTER($A$2:$A, $A$2:$A<>"")) 
  - LEN(REGEXREPLACE(FILTER($A$2:$A, $A$2:$A<>""), SUBSTITUTE($B2, ", ", "|"), ""))
  = LEN(SUBSTITUTE($B2, ", ", "")) 
)))) > 0

The general idea here is to extract all test list elements from a given possible solution list, and then check whether the number of characters removed equals the length of the test list. If that's true, then the test list is contained within the solution list.

The inner ARRAYFORMULA() extends that search scheme to every possible solution list (i.e. to all of column A); the outer ARRAYFORMULA(SUM(N(...))) > 0 returns TRUE if any of the possible solutions pass the test.

userTMKw0
Related