It is Thanksgiving day weekend for our friends & readers in USA. That means a good portion of our readers are relaxing with their family and having a good meal. Naturally, we celebrated thanksgiving at our home (we pretty much celebrate any festival as long as eating good food is one of its rituals). I am too tempted to leave the blog un-updated until Tuesday next week, but then I remembered about remaining good portion of our readers, that is you.
So, here is a simple quiz for you.
Find in which year thanksgiving day occurs on the same date again
using Excel Formulas, of course.
See this short demo to understand what I have in mind:
Thanksgiving day is celebrated on 4th Thursday in November, every year. See how to calculate Thanksgiving day using excel formulas.
Go ahead and find a solution, then post it using comments.
Related Awesomeness: Calculating Common Public Holiday Dates in Excel | Perpetual Calendar – Excel Template
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).