Forum Discussion
Sorting rows with an "x" in them
I'm trying to sort my spreadsheet by multiple columns. How do I indicate to Excel that if a row has an "x" in the "A" column, then that row gets sorted to the bottom of the document? I want to be able to put an "x" in any rows that I've dealt with, and have them go to the bottom of the document. This way I can save them, but they won't be distracting me when I review the rest of the document.
Thank you!
Scott
4 Replies
In addition, with such approach instead of marking cells by "x" and applying conditional formatting rule you may simply fill cell by any color and after that Sort by Color -> No Fill
As variant you may apply conditional formatting rule which highlight entire row or only the field with "x"
Now Sort by Color on No Fill
which gives
Using 365, I do not touch the source data. Instead, I use a formula to produce the reordered data on a separate worksheet.
= LET( current, Table1[x]<>"x", sorted, SORTBY(Table1,current,-1), sorted )Then again, as I am not deleting information from the source data, I could simply filter the displayed data
= FILTER(Table1, Table1[x]<>"x")With legacy Excel similar things can be achieved manually with the Advanced Filter or with PowerQuery.
Scottisimo Sorting a column that contains truly blank cells (i.e. cells with nothing in it) proves to be tricky as these blanks will always be placed last. No matter which sort order you select.
To overcome this you would need to put an empty string in each of the cells in that column first and then over-write these with an X and then, every now and then, sort the table in ascending order of that column. Easiest done when you use a structured table and enter a formula like ="" (i.e. the equal sign and two regular quotation marks) in column A. This will auto-fill the entire column with the empty string formula. When you insert a row, the formula will be copied in automatically.
If you want all to be done automatic, as soon as you have entered an X in column A, you'll need VBA (a macro). Not my kind of thing, I'm afraid.
