Compare two lists only one way?

[CorneliusH]

Hi

I want to have a comparison in a "Valid if" field, where I want to filter all the rows from List A that has an equivalent in List B, but not necessarily the other way around. Let's say

I tried a few formulas and ideas in a Virtual Column and came up with this:

COUNT([AGREGAR_MEZCLA].[LIST_1])-
COUNT(INTERSECT(
LIST_2),[AGREGAR_MEZCLA].[LIST_1]
))=0

Basically I am counting the elements of list 1, and counting the shared elements of both lists; if the result is 0, it is fine; else it should not appear in the Valid If field. However when I paste that formula in the "Valid If" field of the other column and enclose it in a "Select" to get the desired Valid list, it gives me an error:

INTERSECT does not accept a list of list.

How can I avoid that and keep the basic formula? or maybe a better solution?

[Steve]

Try something like this:

ISBLANK([List 1] - [List2])

Returns TRUE is [List 2] includes at least every item in [List 1].

[CorneliusH]

Same result. "Cannot compare list with list of lists". 

Maybe it is because list 2 is a dereference to a different row from the same list 1. What I want to achieve is, to have a list of items that share some components in common. It is something like the ABO blood type: O can be added to A, but A is not ok for O type.

Item	Components	Compatible Items
Item 1	ABC	Item 1, Item 2, Item 8
item 2	ABC	Item 1, Item 2, Item 8
Item 3	ABCD	Item, 1, Item 2, Item 3, Item 4, Item 5, Item 8, Item 9
Item 4	ABCD	Item, 1, Item 2, Item 3, Item 4, Item 5, Item 8, Item 9
Item 5	ABD	Item 5, Item 8, Item 9
Item 6	ABG	Item 6, Item 8, Item 9
Item 7	ABDG	Item 5, Item 6, Item 7, Item 8, Item 9
Item 8	AB	Item 8
Item9	ABD	Item 5, Item 8, Item 9

It feels like another dead end I have to leave open open for now.

[Steve]

Please provide screenshots of the column configurations of the columns involved.