Do you want to sort your data in Google Sheets but in a custom order? The SORT function in Google Sheets allows you to sort your data in ascending or descending order, but it doesn’t provide a direct option to sort in a custom order. Don’t worry, though! In this guide, we will show you how to sort by custom order in Google Sheets using a combination of functions.
The Formula to Sort by Custom Order in Google Sheets
To sort your data in a custom order, we will use either the MATCH or SWITCH function together with SORT. Here are the formulas you can use:
Formula 1: SORT and MATCH combo.
=sort(A2:C10,match(C2:C10,{"Pending";"CDC";"PDC"},0),true)Formula 2: SORT and SWITCH Combo.
=sort(A2:C10,SWITCH(C2:C10,"Pending",1,"CDC",2,"PDC",3),true)You can use either of the above formulas to achieve the desired result. Let’s dive into an example to better understand how these formulas work.
An Example of Custom Sorting in Google Sheets
In the example below, we have a range of data in columns A to C. We want to sort the data based on column C, but in a custom order – “Pending”, “CDC”, and “PDC”.
In cell E2, you can see the formula used to achieve this custom sort order. The sorted data can be seen in column G, where similar items are grouped together based on the custom sort order.
Understanding the Formulas
Now, let’s take a closer look at the formulas used to sort by custom order in Google Sheets.
The SORT function syntax is as follows:
SORT(range, sort_column, is_ascending, [sort_column2, ...], [is_ascending2, ...])In our case, the sort_column argument represents column C, but since we want a custom sort order, we can’t use it directly. Instead, we either generate a virtual column using the MATCH function or the SWITCH function.
The MATCH function syntax is as follows:
MATCH(search_key, range, [search_type])The MATCH function generates a column with the value 1 for “Pending”, 2 for “CDC”, and 3 for “PDC”. This custom sort order column is then used as the sort_column argument in the SORT function.
On the other hand, the SWITCH function does the same thing but in a simpler way:
=SWITCH(C2:C8,"Pending",1,"CDC",2,"PDC",3)The SWITCH function assigns the values 1, 2, and 3 to “Pending”, “CDC”, and “PDC”, respectively.
By using either the generated column from the MATCH function or the SWITCH function as the sort_column argument in the SORT function, we can successfully sort our data in a custom order.
Conclusion
Sorting data in a custom order can be challenging in Google Sheets, but with the help of the MATCH or SWITCH function combined with the SORT function, you can achieve the desired result.
Remember to refer to the custom sorted column (column G in the example) after sorting. You will see that the column retains a grouping format, with similar items grouped together based on the custom sort order.
That’s all for this guide. We hope you found it helpful and are now ready to sort your data in a custom order using Google Sheets. For more tips and tricks, visit Crawlan.com. Stay organized and enjoy the power of Google Sheets!
