Friday, May 6, 2011

yogi_Sort Column A InPlace Based On Customized Order For Entries In Column B

Yogi Anand, D.Eng, P.E.                                   Google Spreadsheet            
RikenJens said:
I'm wondering if this is possible to do. I'd like to create a script to sort data in a spreadsheet by the value in a certain column. The problem is, I want to sort it in a way that is not alphabetical (i.e., there are three values, yes, no, maybe, and I want to sort them as follows: no, yes, maybe. So they are not alphabetical.) Could I assign numbers to the choices, so that 1 = no, 2 = yes, and 3 = maybe, and then sort them numerically? That would make sense, but then would I be able to have 1 be expressed as "no" in the table?
In the following, column A in Sheet1 houses the entries to be sorted by alphabetic entries in column B inthe customized order of first yes entries, then maybe entries, and then no entries. This can be done by adding an intermediary column, say column C (shown in another view of Sheet1 presented here as Sheet1a along with the formula used in cell C1). I have also presented here Sheet1b as another view of Sheet1 where I have reduced the width of the column C, and changed the background color and the font color of column C to yellow.