Please help - how do you test if any of the i...

(Anthony Nel) #1

Please help - how do you test if any of the items in an unsorted list of values in list are in another list ?

i.e if any of the values in the list {3,7,2}

are in the list {2,7,5,3,6,1,3,2} ?

IN or CONTAINS seem to only match the first item in the list

Thanks

(Aleksi Alkio) #2

Is your case exactly like this or is this just an example?

Are you reading values with the SELECT or like TableName[ColumnName]?

(Anthony Nel) #3

Comparing two Virtual Columns of type LIST

(Aleksi Alkio) #4

You’re welcome

(Anthony Nel) #5

Converting to strings:

FIND([V_COL1]&"",[V_COL2]&"")>0

Seems to work fine

(Aleksi Alkio) #6

Are they REF_ROWS?

(Anthony Nel) #7

No they are custom Virtual columns

(Steven Coile) #8
(Aleksi Alkio) #9

I believe your formula would not work correctly. It will only work if the order is exactly the same. If your lists would have only unique values like {2,7,5,3,6,1}, we could check the situation.

(Anthony Nel) #10

Yes, values are always unique, but in random order

(Aleksi Alkio) #11

COUNT({2,7,5,3,6,1}-{3,7,2})=COUNT({2,7,5,3,6,1})-COUNT({3,7,2})

(Anthony Nel) #12

Thanks Aleksi works great!