How to make items rise to the top? [Google Sheets]

Add a custom list: https://support.office.com/en-sg/art...b-ba213ec2fd61

Do you have to sort on the "status" column, or would you be willing to add a helper column and use that to sort on? I could envision a simple lookup table that associates the value 1 with "council", 2 with "enforcer", and so on. then use a lookup function (like VLOOKUP() to add these values to a helper column, then use that column as the sort key.

lookup table

Please Login or Register  to view this content.

lookup function =VLOOKUP(status_text,lookup_table,2,false)

I have moved this thread because the question is specific to Google Sheets.

Code is written in Visual Basic

Visual Basic for Applications -- similar, but not the same.

Visual Basic for Applications -- similar, but not the same.

Yes, so right you are. My bad.


Googling a bit and this is what I found

While macros are currently not supported in Google Docs, Sheets, or Forms, you can extend the capabilities of documents, spreadsheets, and forms by adding custom menus, dialogs, and sidebars with Google Apps Script, a simple yet powerful scripting language based on JavaScript. In Google Sheets, Apps Script also lets you write custom spreadsheet functions.

So it seems macro is out.

I downloaded your "Red Roster" sheet and opened in Excel 2010. Have added the macro and will upload the file. If you have access to Excel 2010 you can download the file from the forum and test to see if a macro like this was what you were looking for.

Alf

Originally Posted by MrShorty

Do you have to sort on the "status" column, or would you be willing to add a helper column and use that to sort on? I could envision a simple lookup table that associates the value 1 with "council", 2 with "enforcer", and so on. then use a lookup function (like VLOOKUP() to add these values to a helper column, then use that column as the sort key.

lookup table

Please Login or Register  to view this content.

lookup function =VLOOKUP(status_text,lookup_table,2,false)

I would be willing to add a helper column, how do I start doing this?

I'm new to this, sorry.

1) Select a safe, out of the way location for the lookup table.
2) Enter the lookup table.

3) Select a column for the helper column (I would probably put it in the column to the right of "steam name".
4) Enter the VLOOKUP() function (MS's help file for this function: https://support.office.com/en-us/art...8-93a18ad188a1 I'm reasonably certain that Google tries to make their sheets compatible with Excel's use of these common, basic functions) Pay attention to your use of relative and absolute references, then copy the formula down the column.

5) Execute sort according Google docs/sheets protocol.

A lookup table is a table that is used in a lookup function. In the help file, after the description of each argument, is a picture that shows a basic lookup table, with last name, first name, and birthday. As I tried to indicate, your lookup table only needs two columns -- status and sort priority.

Assuming google sheets behaves like Excel:
council (without quotes) is the name for a named range, which, I assume you have not defined.
"council" (with quotes) is the text string "council", which, I expect is what you want.
Based on what I saw in your first post, I would expect this argument to be a reference to same row in the "status" column (column C?), instead of using the text literal. So =vlookup(C2,$T$5:$U$9,2,false) Note the use of absolute and relative references so that the reference to the status column will change as the formula is copied, but the reference to the lookup table will not change.

I started the additions. You will need to copy the formula in G3 down the column. You will also need to finish filling in the lookup table (T7:U15).

Maybe I don't. I thought that the end goal was to be able to sort the list so that "council" members would be at the top of the list, followed by "enforcers", and so on. Is that correct?

You are correct that this formula simply returns a number. If done correctly, you should then be able to use this column of numbers as your first sort key. All the 1's should rise to the top, followed by the 2's, and so on.

Is that what you are ultimately looking to do, or is there something I am missing?

A sort key is the column that you want to use for sorting a list. I don't know how these dialogs work in Google Sheets. In Excel, when you click on the "sort" command, a dialog comes up that asks what column you want to use first for sorting the list and how do you want to use that column (ascending or descending). I assume Google sheets will have a similar dialog. In this case, you will use this helper column in column G in the sort dialog when it asks you what column to sort by. If they are sorted in ascending order, then the 1's (which are tied to the "council" value in column C by the VLOOKUP() function) should end up at the top, followed by the 2's and so on.

There seems to be something funny happening when you copy the VLOOKUP() formula down. Instead of using the expected relative reference to the status value (same row column C in each cell), it is changing the reference to row 2. Double check how you are copying the VLOOKUP() formula and make sure that it is correctly adjusting the relative reference.

When you paste it, what is Google doing with the C3 reference in the formula? Spreadsheets (not just Excel) should generally make these references "relative", meaning they move relative to the cell they are pasted from and to. (https://support.office.com/en-us/art...es_in_formulas ). So when you copy this formula from G3 to G4, the reference should change from C3 to C4 as well, and so on down the column. Google does not appear to be doing that. It might be tedious, but if Google does not want to copy according to standard spreadsheet conventions, then you will need to go in and manually correct those references in the VLOOKUP() formula.

The formulas starting in row six are (all):
=VLOOKUP(C2,$T$7:$U$10,2,FALSE), C2 is the header cell (status)
in row three it is correct:=VLOOKUP(C3,$T$7:$U$15,2,FALSE).
As it is copied down the column, C3 must increment, C4, C5, ...

That's what I had in mind. Now, you should be able to execute your sort command, using this column as your first "sort by" (or whatever Google calls it) column.

Whichever cells you want included in the sort. My first guess would be B3:G17.

I'm not sure I understand. What steps are you taking when you add a new member, and what do you expect to happen at each step? When adding a new member, I would expect the following steps:
1) Add data to the bottom of the table.
2) Execute sort command.
3) Table should sort, with new entry(ies) finding its(their) proper place in the table.
Are you expecting your table to sort automatically without manually executing the sort command?

When I go into Google sheets, I see that there is a "sort range" command on the Data menu. I would select the desired range then use the Data menu -> Sort range command. In this dialog, you should be able to indicate which column you wish to sort by and which direction you want to sort (ascending or descending).