We are going to big apple on this weekend, its going to be a really long drive, almost 1000 miles both ways put together, I will see if I can post something while resting, but don’t hope to see anything here for the next 3 days. You have an excellent weekend, drive carefully – more so if you are going anywhere east, stop your car and run towards the closest rest area if you see a compact car (goddamn you rental car company for not telling what car I will be driving) that looks like its chased by grawp. If you are not the kind who uses their right leg and left hand during the weekends, then I am happy for you, do something fun (oh, I am sure you will, afterall you are reading Pointy Haired Dilbert 😉 ), be merry, just watch out if your house is anywhere east. Tada!
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).