Have you ever found yourself frustrated while trying to sort vertically merged cells in Google Sheets? If so, you’re not alone. Unfortunately, Google Sheets doesn’t offer a direct solution for this particular challenge. But fear not! I’ve discovered not one, but two brilliant workarounds that will make sorting a breeze. Get ready to dive in and uncover these ingenious solutions!
Workaround 1: Manually Unmerge and Re-merge Cells
This workaround is perfect for two scenarios: when you want to keep the merged range intact or when the range contains numeric values. Here’s how it works:
- Select the vertically merged cells you want to sort and navigate to Format > Merge cells > Unmerge. You can also use the shortcut icon in the toolbar.
- Next, choose a blank column and right-click to “Copy” the values from the column you wish to sort. Let’s assume you copied them to column D.
- Right-click the first cell in the original merged range (let’s say it’s A2) and select “Paste values”. This action will replace the merged cells with the copied values.
- Now, select the entire range (A2:B15) and use the Sort menu to sort the data. If desired, you can merge the cells again after sorting.
That’s it! You’ve successfully sorted your vertically merged cells using a combination of manual and automatic techniques. Feel free to delete the formula in cell D2 once you’re done.
Workaround 2: Formula-Based Solution
For those who prefer a fully formula-based approach, this workaround is tailor-made for you. Although the output won’t preserve the merged format, you can still achieve the appearance of merged cells when printing the table. Here’s how it works:
-
Slightly modify the formula in cell D2 to include the range B2:B15. The syntax will be
={D2_formula,B2:B15}. -
Use the SORT function to sort column 1 (Item) in ascending order and column 2 (Qty.) in descending order. The formula will look like this:
=SORT({ArrayFormula(lookup(row(A2:A15),if(len(A2:A15),row(A2:A15)),A2:A15)),B2:B15},1,true,2,false) -
It’s advisable to use this formula in cell A2 of a new sheet tab for better organization.
Didn’t quite get it? Let’s assume the original table (A2:B15) and formula (D2) are in Sheet1. In Sheet2, you can copy and paste the field labels using the formula =ArrayFormula(Sheet1!A1:B1) in cell A1.
In cell A2 of Sheet2, insert the modified formula from cell D2 in Sheet1. The formula will now be:
=SORT({ArrayFormula(lookup(row(Sheet1!A2:A15),if(len(Sheet1!A2:A15),row(Sheet1!A2:A15)),Sheet1!A2:A15)),Sheet1!B2:B15},1,true,2,false)The only difference is the sheet name along with the range because the table is in Sheet1 and the formula is in Sheet2. This allows you to effortlessly reference a table in a different sheet.
Bring Back the Merged Look with Conditional Formatting
One drawback of the second workaround is that it loses the merged appearance due to repeated values. However, you can utilize conditional formatting to hide those unwanted values. Here’s how:
- Select the range A2:A15 in Sheet2.
- Go to Format > Conditional formatting.
- Under “Custom formula is,” insert the following rule (formula):
=countif($A$2:A2,A2)>1. - Choose a formatting style that sets the font color to “White” and the background color to “None”.
Voila! You’ve successfully sorted your vertically merged cells in Google Sheets using these genius workarounds. Say goodbye to all the hassle and enjoy the efficiency. And remember, for more amazing Google Sheets tips and tricks, visit Crawlan.com. Happy sorting!
