Sunday, March 26, 2017

yogi_In Query Formulation By OP Sort By List (PRO CAP CLI DOCREP REC)

Google Spreadsheet   Post  #2139
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-26-2017
question by SATH59:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1w_Ma70EteE;context-place=forum/docs
How to sort a query
Hi


=query(arrayformula({
left(Pro!A2:A,3),Pro!A2:D,if(Pro!E2:E="","",to_date(value(Pro!E2:E))),Pro!F2:F;
left(Cap!A2:A,3),Cap!A2:D,if(Cap!E2:E="","",to_date(value(Cap!E2:E))),Cap!F2:F;
left(Cli!A2:A,3),Cli!A2:D,if(Cli!E2:E="","",to_date(value(Cli!E2:E))),Cli!F2:F;
left(Doc!A2:A,3),Doc!A2:D,if(Doc!E2:E="","",to_date(value(Doc!E2:E))),Doc!F2:F;
left(Rep!A2:A,3),Rep!A2:D,if(Rep!E2:E="","",to_date(value(Rep!E2:E))),Rep!F2:F;
left(Rec!A2:A,3),Rec!A2:D,if(Rec!E2:E="","",to_date(value(Rec!E2:E))),Rec!F2:F
}),"Select Col2,Col3,Col4,Col5,Col6,Col7 where Col5 <>'' and Col5 contains '"&B1&"' order by Col1, Col6 desc,Col3 ",0)


So this query is sorted by col 1 and it works, but i allways get the first column sorted by A to Z
Cap, Cli, Doc, Pro, Rec, Rep

What i need is to have it that whay but i want it to sort in different order, for example
First "Pro", then "Cap", then "Doc" then "Rep" then "Cli" ... i want to be able to sort them A to Z but in a different category order

I hope im explainig this well 

How can i achieve this ?

Thanks !!!