Monday, July 11, 2011

yogi_ Auto Sort Data By Multiple Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Zorkian said:
How can I have a spreadsheet auto sort by multiple collumns?
So, I have a spreadsheet that I work on daily, and I want to have it auto-sort rows alphabetically based on multiple collumns. How would I go about doing this in google docs?
the basic layout is that there are many data sets, with information for each individual organized in a row. the first few collums give some sorting information, and the rest just needs to stay with that information.
any tips?
I guess Ill make a little drawing to demonstrate what i mean
Data1 - Data2 - Data3 - Name -   information
y       y               Alpha    blah
y               y       Bravo    blha
        y       y       Charlie  balh
                y       Delta    bahl
y       y       y       Echo     bhal
y                       Foxtrot  bhla
        y               Golf     hbla
                        Hotel    hbal
and when sorted, I want it to look like this ->
Data1 - Data2 - Data3 - Title -  information
y       y       y       Echo     bhal
y       y               Alpha    blah
y               y       Bravo    blha
y                       Foxtrot  bhla
        y       y       Charlie  balh
        y               Golf     hbla
                y       Delta    bahl
                        Hotel    hbal
the only thing is, the data in the Data1 collumn gets regularly changed, so I want the spreadsheet to autosort (data1 is sort of like a pertinence flag)
------------------------------------------------------------------------
In the following solution, the original data is in column A:E. I have used a formula based approach to recreate the original data in columns G: K in sorted order by column 3 in descending order, by column 2 in descending order, and by column 1 in descending order.