May 31 2023 12:55 PM
Hi,
Is it possible to sort a column from smallest to largest but keeping every other row which is a blank grey filled line in place? Currently when I sort it will sort the data but removed the gap spacer row each time
For example currently it is this:
column 1
55
(blank grey filled row)
2
(blank grey filled row)
37
(blank grey filled row)
But I would like it to sort like this:
Column 1
2
(blank grey filled row)
37
(blank grey filled row)
55
(blank grey filled row)
I don't have the best excel knowledge so please if anyone can help or let me know if this is possible.
Thank you!! :)
May 31 2023 01:01 PM
Excel doesn't sort that way, but you could do the following:
Select column A.
Press F5 to activate the Go To dialog.
Click Special...
Select Blanks, then click OK.
Note the address of the active cell - probably A2.
Enter the formula =A1 (referring to the cell above the active cell) and confirm with Ctrl+Shift+Enter.
Select A1.
Sort in ascending order.
May 31 2023 07:29 PM
Jun 02 2023 12:32 PM
A formula solution:
=LET(order,SCAN("",values,LAMBDA(a,v,IF(v="",a,v))),SORTBY(values,order))
Custom format the cells to pull the 0s returned from the array and you're set.
Jun 02 2023 06:13 PM
SolutionAn alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
Jun 03 2023 03:29 AM
Jun 02 2023 06:13 PM
SolutionAn alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.