0

Conditional sorting

Yvonne Jury

October 12, 2017 17:03

Answered

Follow

Hello, I need help on figuring out how to do a conditional sort.  I'm trying to do a conditional sort on serial no. where If the transaction type is not RELEASE, sort "After serial no." where Type = "To", else sort "Before serial no." where Type = "From".  This formula works: 

=NL("Rows","posted work order line",,"document no.",NP("Union",$E$14,$E$15,$E$16),"+work order date",$D$3,"+=if(nf(,""Transaction Type"")<>""RELEASE"",NL(""Last"",""posted work order line"",""After serial no."",""Document no."",nf(,""Document no.""),""Type"",""To""), NL(""Last"",""posted work order line"",""Before serial no."",""Document no."",nf(,""Document no.""),""Type"",""From""))","*")

However, I need to add more logic.  What I really need to do is if Transaction Type is not RELEASE, then sort "After Serial no." where Type = "To".  Then if Type is = RELEASE, and if "After serial no." is not zero where Type = "From", then sort on that, else sort on "Before Serial no." where Type = "From".  So I added the additional logic into the sort like below, but it doesn't work.  It returns #VALUE (Excel is unable to evaluate).

=NL("Rows","posted work order line",,"document no.",NP("Union",$E$14,$E$15,$E$16),"+work order date",$D$3,"+=if(nf(,""Transaction Type"")<>""RELEASE"",NL(""Last"",""posted work order line"",""After serial no."",""Document no."",nf(,""Document no.""),""Type"",""To"")," & "
if(nl(""last"",""posted work order line"",""After Serial No."",""Document No."",nf(,""document no.""),""Type"",""FRom"")<>0, NL(""Last"",""posted work order line"",""AFter serial no."",""Document no."",nf(,""Document no.""),""Type"",""From"")," & "NL(""Last"",""posted work order line"",""Before serial no."",""Document no."",nf(,""Document no.""),""Type"",""From"")))","*")

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis October 12, 2017 21:26

  Hi Yvonne -

  Rather than use the concatenation symbol (&), what you probably would need to do is use Excel's AND function.

  So, essentially:

  =IF(AND(condition1,condition2,condition3), what to return if all conditions are TRUE, what to return if any condition is FALSE)

  Of course, your specifics will be a tad more complicated.

• 0
  
  Yvonne Jury October 12, 2017 22:05

  There are actually three possibilities of what to return, not just two.

  It can't imagine it could be that complicated, but I just can't get it working.  To simplify what my formulas are trying to do:

  If Type = Release and From After Serial No. = 0 then get From Before Serial No.

  If Type = Release and From After Serial No. <> 0 then get From After Serial No.

  If Type <> Release then get To After Serial No.

  And then sort on the results!

   

  Edited by Yvonne Jury October 12, 2017 22:08
• 0
  
  Harry Lewis October 12, 2017 22:23

  The syntax for that could be something like this:

   

  =IF(Type<>Release,get TO_After_Serial_No, IF(Serial_No=0,get From_BEFORE_Serial_No.,get From_AFTER_Serial_No.))

• 0
  
  Yvonne Jury October 19, 2017 18:57

  I was finally able to get the sort working properly.  One change I had to make was to use the table and column numbers instead of names to avoid the 255 character limit.

  =NL("Rows","posted work order line",,"document no.",NP("Union",$F$15,$F$16,$F$17,"+work order date",$D$3,"+=if(nf(,""4"")<>""RELEASE"",NL(""Last"",""16020671"",""199"",""1"",nf(,""1""),""2"",""To""),if(and(nf(,""4"")=""RELEASE"",nf(,""199"")<>0,nf(,""2"")=""From""),NL(""Last"",""16020671"",""199"",""1"",nf(,""1""),""2"",""From""),NL(""Last"",""16020671"",""198"",""1"",nf(,""1""),""2"",""From"")))","*"