Compare one list with a table

[Cela]

Hi everyone,

my task is to check whether the list (which will play the role of the source in terms of a look up) contains entries that are not listed in a specific column of the designated table.

Here a simplified example as to how the column of the table could look like:

(Table)

Column X

1, 2, 3, 4, 5, 8, 10

3, 6, 8

(List)

1

2

3

4

5

6

7

8

9

10

So in the case above, I would need to get somehow the result that 7 and 9 are not listed in the table.

The challenge here is to check if the numbers appear at least once in any of the rows in the column. I was trying formula approaches with the !contains() expression and similar methods but I seem to end up in a dead end.

Any possible approaches would be highly appreciated.

[gawa · Accepted solution]

hi @Cela 

Here is the solution to your demand. Key is to split data into row, and compare by JOIN tool. You'll find non-exist number from R-anchor of JOIN tool.

[Cela]

@gawa Thanks a lot! I was able to modify it fairly easy to  my use case and it works perfectly fine, I appreciate it!