Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with Column has Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with Column has Formula
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with Column has Formula
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" wrote: 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 wrote: 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with Column has Formula
Or use another column:
=if(cellwithformula="",rept("z",255),cellwithformu la) 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. Neon520 wrote: 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" wrote: 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 wrote: 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 . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a Pivot Table Column that is not the first column... | Excel Worksheet Functions | |||
Column sorting | Excel Discussion (Misc queries) | |||
How do I limit sorting a column to the column? | Excel Worksheet Functions | |||
Sorting 1 column into two | Excel Discussion (Misc queries) | |||
Right column doesn't change when sorting left column. | Excel Discussion (Misc queries) |