Find items in a list that contain all sub-items

Question

I have two lists as follows, Column A and Column D:

I would like to find all cells in Column A that do not contain a value from Column D.

For instance, A1 should be 0 because it contains the values 'a', 'b', 'c' and 'd' - all of which are in Column D.

A2 should be 1 because it contains the value 'h' - which is not in Column D.

My formula so far is very simple:

=COUNTIF(D1:D7,"*"&$A1&"*")

I'm guessing I could split the values in Column A to check, but am not too sure how.

I would like to avoid any VBA if possible.

score 2 · Accepted Answer · answered Dec 26 '16 at 20:28

2

Your question is not entirely clear to me, so far as what you want for results under different circumstances.

Your formula will return an array of values, so you need to account for that. The array consists of a 1 or a 0 depending on whether the character matches a letter in the D1:D7 range.

If you want to return the number of letters in your column "A" item that do NOT match an entry in Column "D", then try:

=LEN(A1)-SUMPRODUCT(COUNTIF(A1,"*"&$D$1:$D$7&"*"))

The SUMPRODUCT functions sums the array that the COUNTIF function is returning.

If you want something else, you will need to be more specific.

answered Dec 26 '16 at 20:28

Ron Rosenfeld

53,870
7
28
60

I have hopefully clarified my question – rhughes Dec 26 '16 at 21:40
@rhughes How does your explanation differ from what I assumed in providing my formula? Where does my formula not meet your needs? – Ron Rosenfeld Dec 26 '16 at 21:43

score 1 · Answer 2 · answered Dec 26 '16 at 16:27

1

Cross-tabular layout

Using your logic, just with different layout of the data, you can achieve this:

Formula for B4 is: =COUNTIF(B$3;"*"&$A4&"*")
Formula for B1 is: =B2-SUM(B4:B10)

answered Dec 26 '16 at 16:27

kolcinx

2,183
1
15
38

Your solution is very interesting! The marked answer was easier to implement though. Thank you again. – rhughes Dec 27 '16 at 11:30
You're welcome. I agree, Ron's answer is superior. I was amazed by it. – kolcinx Dec 27 '16 at 14:49

score 1 · Answer 3 · edited May 23 '17 at 12:33

This is more along the lines of a comment, but I don't have enough reputation yet for comments.

The accepted answer from @ronrosenfeld will not work if the string in Column A contains repeated characters from the list in Column D. For example, if A1 contains "abca," it will return 1 rather than 0 because the array entry for "a" is 1 rather than 2 (it can only be 0 or 1).

So be aware that it only works if the letters are not repeated.

I cobbled a formula together based on some array magic I found by @ronrosenfeld here. (It seems so appropriate that Ron already got credit for answering this question, as my answer is a modification of another of his.)

The following formula works for any length of string in Column A and for any combination of letters including duplicates. It is entered as a regular formula:

=IFERROR(SUMPRODUCT(MATCH(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1),D$1:D$7,0)=1),1)

You just enter it in B1, then copy it down as far as you like.

It works for strings of any length. If a cell is blank, it returns 1 because there is nothing there that appears in the list. If you want 0 for a blank cell, you can adjust the formula for that situation. Brute force approach:

=if(isblank(a1),0,IFERROR(SUMPRODUCT(MATCH(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1),D$1:D$7,0)=1),1))

Find items in a list that contain all sub-items

3 Answers3

Cross-tabular layout