Identifying values that are NOT in another table

Showcase
1

A Grist creator recently asked for help finding values in a column that are not present in another column in another table. I’d like to share my example in case it’s useful to you.

Suppose you have a table called ID and table called SKU. You have a column of IDs in the ID table and a column of SKUs in the SKU table. You want to see which IDs are not present in the SKUs column.

  1. In the ID table, create a new column called “Not in SKU?”
  2. Enter this formula.
if SKU.lookupOne(SKU=$ID2):
  return False
else:
  return True

This formula is looking up values in the SKU table’s SKU column, and matching them to values in the ID table’s ID column. If it finds a match, it returns False because the value is in the SKU table, and in this column we only care about values that are not in the SKU table. If the lookup does not find a match, then the formula returns True because it is true that the value is not in SKU. :brain::pretzel:

  1. Change the “Not in SKU?” column type to a toggle column and format it so that it is easier to scan at a glance. I opted for red switches.

You may view the example here: https://public.getgrist.com/uw3VZbSk3LQn/Identifying-values-NOT-in-another-table

ETA: In step 2, the formula could also be written much more simply:
not SKU.lookupOne(SKU=$ID2)
This formula will return True when there is no match, and False when there is a match.

2 Likes