[Solved] Ignoring null cells in the sort function

[Anthony]

Hello everyone, i hope i'm not posting this in a wrong channel, but i have a question:

I'm currently working on a file for cataloguing data on clients.
One of my columns contains a formula that spits out -null- spaces under some conditions (on purpose) and now i'm encountering the issue, that when using the sort function, these cells appear on top, hurting the functionality of the file.

This file is made to be used by excel novices, so i'm trying to build it in a way that no one in the coming years would have to know how to format/ edit formulas.

Is there a way to force the sort function to ignore blank cells without it also ignoring future inputs?
/ is there a way to have a formula spit put -blank- instead of -null-? (Which, as far as i can tell, would also solve my issue)


Apologies in advance for not attaching a file, but it contains too much sensitive information for me to attach, and deleting all content would take a while.

 

[Yousuf Shovon]

Is there a way to force the sort function to ignore blank cells without it also ignoring future inputs?
/ is there a way to have a formula spit put -blank- instead of -null-? (Which, as far as i can tell, would also solve my issue)

Hello Anthony,

Welcome to ExcelDemy Forum! Thank you for your question.

I understand you are facing the SORT and Null issues. I have found a way to have a formula return a blank cell instead of a null cell using SORT and IF functions.

I have tried to create a dataset based on your description. Where the IF formula returns a null cell when the Client score is less than 60.

​

Consequently, we obtain null cells in column C.

Now, we want to assign the null cells as blanks and sort them using the SORT function.

• Enter the below SORT and IF functions in E2:

=SORT(IF(A2:C11=" ", "", A2:C11), 3, TRUE)​

As a result, we obtain the non-blank data sorted in column G.

There is another way to fix the issue by creating a Helper Column first. Afterward, sort the range based on the new column with the below formula:

• First, create the Helper Column using the below formula in C2:

=IF(B2="","z","a")&B2

​

• Later, sort the new column using this formula:

=SORT(B2:C10,2,1)

​

See the below article, to learn more about SORT and blank issues:

Excel Sort and Ignore Blanks​

Hope either of these methods assists you in fixing your issue. I am attaching the Excel file for a better understanding.

Regards,
Yousuf Shovon