Are you tired of the same old ascending or descending order when sorting your data in Google Sheets? Do you want to create your own unique custom sort order? Well, my dear friends, you’re in for a treat! In this article, I will reveal a simple yet effective method to sort your data in Google Sheets according to your very own custom order.
The Magic Formula
But before we dive into the nitty-gritty, let me introduce you to the magic formula that will make custom sorting a breeze. Brace yourselves, here it is:
=sort(A2:C10,match(C2:C10,{"Pending";"CDC";"PDC"},0),true)Or if you prefer a slightly different approach, you can use this formula instead:
=sort(A2:C10,SWITCH(C2:C10,"Pending",1,"CDC",2,"PDC",3),true)You can choose either of these formulas to achieve the same amazing result. Now, let’s break down how this magical formula actually works.
Formula Explanation
The formula starts with the SORT function, which is a powerful tool for sorting data in Google Sheets. It requires two main arguments: the range of data to be sorted and the sort column.
In our case, the sort column is Column C, where we have the values “Pending”, “CDC”, and “PDC”. However, since we want a custom sort order, we can’t use the column directly.
To achieve the custom sort order, we utilize either the MATCH or SWITCH function. These clever functions generate a virtual column that assigns a value of 1 for “Pending”, 2 for “CDC”, and 3 for “PDC”.
Once we have this virtual column, we can use it as the sort column in the SORT function. Voila! Our data is now sorted according to our custom order.
And there you have it! With this simple yet powerful formula, you can easily sort your data in Google Sheets according to your very own custom order.
An Example
To better understand how this formula works, let’s take a look at a practical example. In the screenshot below, you can see that I’ve sorted the data in the range A2:C8 based on column C, following this custom order: “Pending”, “CDC”, and “PDC”.
You can find the formula used in cell E2. As you can see in column G, the data is now beautifully sorted according to our custom order.
Conclusion
Sorting data in Google Sheets is a common task, but sometimes the usual ascending or descending order just doesn’t cut it. If you want to sort your data in a custom order that suits your specific needs, the formula I’ve shared in this article will be your new best friend.
Remember to always refer to the custom sorted column, as it maintains a grouping format that allows similar items to be grouped together based on your custom sort order.
Now you have a powerful tool in your Google Sheets arsenal. So go ahead and give it a try! Enjoy the benefits of custom sorting and take full control of your data.
Stay tuned for more juicy secrets and handy tips, exclusively at Crawlan.com.
