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.
- In the
IDtable, create a new column called “Not in SKU?”
- Enter this formula.
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.
- 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:
This formula will return True when there is no match, and False when there is a match.