Today, we will learn an interesting array formula trick to test if a list is sorted or not.
During last one week, I got 2 requests from different clients for some excel related work. Both of them had one thing in common. To test whether a list is sorted or not. So I got thinking, how do I know if a list is sorted or not without visually scanning it.
Of course, the answer is in a formula.
Let us say you have a list of values (text, numbers or mixed) like this:
Assuming your values are in the range B2:B8
Write the formula =AND(B2:B7<=B3:B8) and press CTRL+Shift+Enter to make it an array formula.
The result will be true if the list in B2:B8 is sorted in ascending order, false otherwise.
How does this formula work?
Very simple. Assuming your list has n values, it check first n-1 values with their next values to see if each value is less than or equal to its next value. If all of them are true, the AND formula returns TRUE. See this illustration:
Download Example Workbook
I have made a simple example file to illustrate this technique. Go ahead and play with it.
Click here to download example workbook – Excel Array Formula to check if a list is sorted or not.
How would you write formula for this?
As fun homework, go ahead and figure out an alternative formula for this. Then come back and post it comments. Let us see how many different ways we can get the same result.
Go!
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).