Sorting with Column has Formula

[Neon520]

Hi everyone,

I never imagined that the formula in the column would affect the sorting
order in any way, but it does in my case.

Below is the formula in that I have in Col I, and I'd like to sort it in
Ascending order, but the result is that it sorts with all the empty rows on
top and the one with the result from the formula at the bottom. I assume it
consider the "I" in the "IF" function in the formula, but I'm not sure.

Can anyone tell me how to fix this please?

=IF(E2="","",IF(J2="X","Priority
#1",IF(ISNA(VLOOKUP(E2,PriorityList,4,0)),"No Priority
Found",VLOOKUP(E2,PriorityList,4,FALSE))))

Thank you very much,
Neon520

 

[Dave Peterson]

There's a difference between the way excel sees really empty cells and formulas
that evaluate to an empty string.

Numbers, then strings, then empties.

And the string ="" pops to the top of the strings.

Maybe you can modify your formula to return a different value:
=if(e2="",rept("z",99),...

or even fill in the truly empty cells with formulas (="").

 

[Neon520]

Hi Dave,

Your suggestion sounds fair, except that I don't want Col I to have anything
if Col E is empty.
I understand that by putting zzzzzz at if the evaluation is true, it will
sort those records to the bottom of the list, but it wouldn't be pleasant to
have it viewed that way.

Is there anything I should change in my function to make this work?

Maybe instead of putting "" if the evaluation is true, I should put
something else?

Thank you,
Neon520

 

[Dave Peterson]

Or use another column:

=if(cellwithformula="",rept("z",255),cellwithformula)

like:
=if(x2="",rept("z",255),x99)
and drag down

Then sort the entire range by this column.

You could hide or delete the column when you're done.