# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  >  How to make items rise to the top? [Google Sheets]

## Idler

I would like to make people with rank "Council" to automatically rise to the top, people with rank "Enforcer" to go after them, followed by "Member" and then "Trialist". How do I do this?

0b9a8862f2e4750dfbd70a6a1a286e92.png

http://www.excelforum.com/attachment...4947-how-to-co

----------


## Alf

You could try a macro like this




```
Please Login or Register  to view this content.
```


Right click on tab "Red Roster", then click on "View Code". In the new windows that opends past this code.

This will sort based on the value in the C, it's possible to add a second sort key for instance if you wish to sort on both position and name.

As this macro is written it will only be activated by a change in range C3:C27.


Alf

----------


## shg

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

----------


## Idler

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



Any idea how to do this in Google Docs?

----------


## Idler

> You could try a macro like this
> 
> 
> 
> 
> ```
> Please Login or Register  to view this content.
> ```
> 
> ...



Can't find how to do this in Google Sheets....

----------


## Alf

I'm sorry but as I've never worked with Google sheets  I can't help you any further. 

Alf

----------


## Idler

> I'm sorry but as I've never worked with Google sheets  I can't help you any further. 
> 
> Alf



All the formulas are the same, it's just there's no code thing. There's a powerful script editor though.

What language is that bit of code in?

----------


## 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)

----------


## Alf

> What language is that bit of code in?



Code is written in Visual Basic

Alf

----------


## 6StringJazzer

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

----------


## shg

> Code is written in Visual Basic



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

----------


## Alf

> 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

----------


## Idler

> 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.
> ```
> 
> ...



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

I'm new to this, sorry.

----------


## MrShorty

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.

----------


## Idler

> 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.



Sorry, what is a lookup table, and how is it formatted? Sorry  :Frown:

----------


## MrShorty

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.

----------


## Idler

I used =VLOOKUP(council,T5:U9,2,false)

And put this: 
a7c161b47f19bbc254452dfe29f0e074.png
as the lookup table.

However, I got
d238e067c9bd7afefabd9eba7e7839c5.png

and 

8aaf7499de1b3a5500b46d30f3a21814.png

----------


## MrShorty

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.

----------


## Idler

Sorry, I'm completely baffled as to what I'm supposed to do.

Can you fill in the lookup table for me and then I'll work out how it works?
Thanks so much for all this help.
RED Roster (5).xlsx

----------


## MrShorty

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).

----------


## Idler

This really isn't helping me... All that happens is that a "1" appears. I'm not quite sure that you know what I want to do...

MrShortyCopy of RED Roster (5) (1).xlsx

----------


## MrShorty

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?

----------


## Idler

> 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?



That is what I'm looking for, but I don't know what a sort key is?

----------


## MrShorty

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.

----------


## Idler

OK, nice. I tried to do that, but it's not working...

MrShortyCopy of RED Roster (5) (2).xlsx

----------


## MrShorty

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.

----------


## Idler

> 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.



I'm just copy and pasting it...

----------


## MrShorty

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.

----------


## protonLeah

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, ...

----------


## Idler

Ah. So I've done that, and I got this. Is this right?

RED Roster (6).xlsx

----------


## MrShorty

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.

----------


## Idler

What should I select to sort? Which set of cells?

----------


## MrShorty

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

----------


## Idler

Alright, I sorted it but when I make a new member, with the status council, it doesn't rise to the top.

----------


## MrShorty

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?

----------


## Idler

> 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?



That is what I was expecting, but how do I execute the sort command?

----------


## MrShorty

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).

----------


## Idler

Ah, I don't really want to do that every time, so I'm going to give up on this.

Thanks everyone for the help, really appreciate it!

----------

