Sort Function (SortBy) - Trying to get empty cells to return empty cells using the sort function

%3CLINGO-SUB%20id%3D%22lingo-sub-1344560%22%20slang%3D%22en-US%22%3ESort%20Function%20(SortBy)%20-%20Trying%20to%20get%20empty%20cells%20to%20return%20empty%20cells%20using%20the%20sort%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344560%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20just%20and%20aesthetic%20issue.%3C%2FP%3E%3CP%3EI%20have%20a%20table%20with%20around%207%20headings%20for%20a%20simple%20account%20information%20table.%20Not%20all%20accounts%20have%20a%20value%20for%20each%20heading%2Ffield%20so%20on%20entering%20data%20those%20cells%20are%20left%20blank.%20To%20make%20it%20easier%20to%20find%20each%20row%20of%20account%20information%20I%20have%20used%20the%20SORT%20function%20so%20I%20can%20continue%20to%20add%20new%20accounts%20on%20the%20original%20table%20as%20I%20think%20of%20them%20and%20they%20are%20displayed%20alphabetically%20on%20a%20different%20sheet%20as%20the%20table%20is%20updated.%3C%2FP%3E%3CP%3EThe%20displayed%20results%20from%20the%20SORT%20function%20return%20%220%22%20zero%20in%20every%20field%20which%20is%20blank%20in%20the%20original%20table%20and%20as%20the%20Date%20column%20is%20formatted%20to%20display%20a%20date%20it%20returns%2000%2F01%2F1900.%20I%20would%20like%20it%20to%20return%20blank%20cells%20from%20blank%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20of%20getting%20the%20SORT%20function%20to%20return%20a%20blank%2Fempty%20cell%20or%20it%20simply%20a%20result%20of%20the%20entries%20being%20processed%20as%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1344560%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1344706%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20Function%20(SortBy)%20-%20Trying%20to%20get%20empty%20cells%20to%20return%20empty%20cells%20using%20the%20sort%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1344706%22%20slang%3D%22en-US%22%3EPerhaps%20you%20want%20to%20look%20at%20the%20FILTER%20function%20instead.%3CBR%20%2F%3E%3CBR%20%2F%3EAn%20example%20of%20filtering%20a%20Table%20by%20'Col1'%20having%20blanks%20and%20returning%20a%20sort%20of%20the%20entire%20Table%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSORT(FILTER(Table1%2CTable1%5BCol1%5D%26lt%3B%26gt%3B%22%22))%3CBR%20%2F%3E%3CBR%20%2F%3EAn%20example%20of%20filtering%20a%20Table%20by%20'Col1'%20and%20'Col2'%20having%20blanks%20and%20returning%20a%20sort%20of%20the%20entire%20Table%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSORT(FILTER(Table1%2C(Table1%5BCol1%5D%26lt%3B%26gt%3B%22%22)*(Table1%5BCol2%5D%26lt%3B%26gt%3B%22%22)))%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20FILTER%20'include'%20parameter%20can%20take%20an%20array%20of%20boolean%20returns%2C%20for%20example%3A%3CBR%20%2F%3E%3CBR%20%2F%3E(CriteriaRange1%3DCriteria1)*(CriteriaRange2%3DCriteria2)*(CriteriaRange3%3DCriteria3)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2427219%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20Function%20(SortBy)%20-%20Trying%20to%20get%20empty%20cells%20to%20return%20empty%20cells%20using%20the%20sort%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2427219%22%20slang%3D%22en-US%22%3EHi%20there%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20believe%20this%20can%20be%20solved%20with%20a%20simple%20if%20statement%20-%20I%20just%20ran%20into%20the%20same%20issue%20and%20had%20to%20solve%20it.%3CBR%20%2F%3E%3CBR%20%2F%3E%3Dif(isblank(Sort(*your%20sort%20parameters*))%2C%20%22%22%2C%20Sort(*your%20same%20sort%20parameters*))%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20that%20helps%20you%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E
New Contributor

This is just and aesthetic issue.

I have a table with around 7 headings for a simple account information table. Not all accounts have a value for each heading/field so on entering data those cells are left blank. To make it easier to find each row of account information I have used the SORT function so I can continue to add new accounts on the original table as I think of them and they are displayed alphabetically on a different sheet as the table is updated.

The displayed results from the SORT function return "0" zero in every field which is blank in the original table and as the Date column is formatted to display a date it returns 00/01/1900. I would like it to return blank cells from blank cells.

 

Is there anyway of getting the SORT function to return a blank/empty cell or it simply a result of the entries being processed as data?

2 Replies
Perhaps you want to look at the FILTER function instead.

An example of filtering a Table by 'Col1' having blanks and returning a sort of the entire Table:

=SORT(FILTER(Table1,Table1[Col1]<>""))

An example of filtering a Table by 'Col1' and 'Col2' having blanks and returning a sort of the entire Table:

=SORT(FILTER(Table1,(Table1[Col1]<>"")*(Table1[Col2]<>"")))

The FILTER 'include' parameter can take an array of boolean returns, for example:

(CriteriaRange1=Criteria1)*(CriteriaRange2=Criteria2)*(CriteriaRange3=Criteria3)
Hi there,

I believe this can be solved with a simple if statement - I just ran into the same issue and had to solve it.

=if(isblank(Sort(*your sort parameters*)), "", Sort(*your same sort parameters*))

Hopefully that helps you