Have you ever wanted to sort each row in a 2-D array in Google Sheets? Well, you’re in luck! In this article, I will show you how to achieve row-wise sorting using an array formula in Google Sheets. No more manual sorting or complex formulas. Let’s dive in!
The Power of Row-Wise Sorting
Row-wise sorting is the process of sorting each row in a 2-D array individually. By using array formulas in Google Sheets, we can easily accomplish this task. In this article, we will explore two methods: BYROW and REDUCE.
BYROW: The Recommended Solution
BYROW is my recommended solution for row-wise sorting in Google Sheets. It is simple, efficient, and gets the job done. Here’s how it works:
- Let’s say we have a 2-D array in the range B2:E5.
- To sort each row in ascending order, use the following array formula in cell B9:
=BYROW(B2:E5,LAMBDA(row,TRANSPOSE(SORT(TRANSPOSE(row),1,1))))
- If you prefer descending order, use this formula instead:
=BYROW(B2:E5,LAMBDA(row,TRANSPOSE(SORT(TRANSPOSE(row),1,0))))
The difference between the two formulas is negligible, as you can see. Feel free to enter the formula in any cell and give it enough room to expand.
How Does BYROW Work?
The BYROW function applies a lambda to each row in the array and returns an array of the results. Here’s the syntax:
BYROW(array_or_range, lambda)
array_or_range: B2:E5 (rows separated individually).lambda: A formula expression that sorts the row.
The formula expression, TRANSPOSE(SORT(TRANSPOSE(row),1,1)), transposes the row, sorts it, and then transposes it back. This process is applied to each row in the array. Simple, right?
Additional Tip: TOCOL and TOROW
To make the formula more concise and easier to understand, we can use the TOCOL and TOROW functions instead of TRANSPOSE. Here’s the formula expression:
TOROW(SORT(TOCOL(row), 1, 1))
The TOCOL function converts a range of cells into a single column, while the TOROW function converts a range of cells into a single row. By using these functions, we can avoid using TRANSPOSE twice.
I hope this explanation has helped you understand how to use the BYROW function for row-wise sorting in a 2-D array in Google Sheets.
REDUCE: Another Approach
Although BYROW is my preferred method, I want to introduce you to another approach using the REDUCE function. Let’s take a look:
- Consider the 2-D array in the range B2:E5.
- Use the following formula in any cell to achieve row-wise sorting:
=REDUCE(,SEQUENCE(ROWS(B2:E5)),LAMBDA(a,v,IFNA(VSTACK(a,TOROW(SORT(TOCOL(CHOOSEROWS(B2:E5,v))))))))
This formula may be more complex to code, but it also provides row-wise sorting. Keep in mind that it returns an empty row at the top of the result.
How Does REDUCE Work?
The REDUCE function applies a lambda to each element in an array or a range. Here’s the syntax:
REDUCE(initial_value, array_or_range, lambda)
initial_value: null.array_or_range: SEQUENCE(ROWS(B2:E5)) (serial numbers representing rows).lambda: A formula expression that sorts the row.
The formula expression, IFNA(VSTACK(a,TOROW(SORT(TOCOL(CHOOSEROWS(B2:E5,v)))))), stacks the accumulator (initially null) with each sorted row. The CHOOSEROWS function selects the row, converts it into a column using TOCOL, sorts the column, and then transforms it back into a row using TOROW.
To put it simply, the formula sorts each row individually and accumulates the results in the final value.
And there you have it! Two methods to achieve row-wise sorting in a 2-D array in Google Sheets. Choose the one that suits your needs and start sorting with ease.
You can find more helpful tips and tricks like this on Crawlan.com. Enjoy your Google Sheets journey!
