# How Can I Sort a Data Range in Google Sheets Based on an Data Range?

I have been looking for a way to sort this information. I need to sort these items in this order: `BK, BB, BV, BIV, BIII, BII, BR`. I was trying to make a Google Script to get it, but I could only figure out how to order by name. I need it to be automatically ordered and on a specific sheet. Screenshots below: Also since it wasn't in the screenshot, the sheet name is Sheet1.

• when you say "automatically ordered" do you mean you do not want to click "Sort sheet..." in the column header menu every time you want it to be sorted? Do you want it to be sorted on the fly as you enter or change the data? – Andrej Adamenko Mar 22 '19 at 4:08
• I need it to be sorted as I enter data, but I can't figure out a way to sort it based on specific names. – danstyx Mar 22 '19 at 5:03

1. Create and fill sort order numbers (column D on the screenshot)

2. Enter the following formula to cell F2:

=SORT(ARRAYFORMULA({\$A2:A , IF(\$A2:A="", "", VLOOKUP(\$A2:A,\$C\$2:\$D\$8,2,FALSE))}),2,TRUE)

It will fill and maintain sorted data in columns F and G automatically. If you do not want column G, you can hide it.

If you do not want a sorted copy of your data, and you want to sort the data in place, you can add a column B, fill it with sorted order numbers (generated by `VLOOKUP()`) and then click "Sort sheet A-Z" on the column B header every time you need to sort the data. However you will need to move "Data Range" a separate sheet. Otherwise they will be broken by sorting the sheet. • The equation portion is exactly what I need, but I need it to rearrange the cells with the dropdown in order. Following their position, they will have other information attached to their "Position." Similar to this: gyazo.com/9893b0e1e3306a16005396bd98c8e47d Sorry to be a bother, just been stuck on this one spot. To sum it up, when I change the dropdown, I need it to auto sort it in the same column. – danstyx Mar 22 '19 at 4:57
• I believe you can do it with a script which could be assigned to be executed every time you change the data – Andrej Adamenko Mar 22 '19 at 5:26
• I found a work around, but how can I reference the data to be sorted from another sheet? What would I have to change this code to? Do I just add Sheet2!\$C\$2 to reference to another sheet? – danstyx Mar 22 '19 at 5:36
• Cool thank you, I got it working :D – danstyx Mar 22 '19 at 5:52

You can set a second column (commonly called a "helper column") with values for each one, then order by Ascending value by the value column. E.g. cell B2 (BK) should be '1'. B7 (BB) print '2'. B9 (BV) put '3' in the cell. If you only have one column and the contents are text, it can only be sorted alphabetically or reverse alphabetically.

``````=ARRAYFORMULA(INDEX(SORT({A2:A, IF(A2:A="", ,
VLOOKUP(A2:A, {C2:C, ROW(C2:C)}, 2, 0))}, 2, 1), , 1))`````` to sort an existing range based on another range and without creating a new range for that purpose use this formula in B2 cell:

``=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, {C2:C, ROW(C2:C)}, 2, 0)))``

then select a range to be sorted with the additional numbers and do: and when done you can delete column B

• Thanks, this is what I need and I could find a workaround, but is there anyway I can make it so instead of having to make another column for sorted, it could just move the data? – danstyx Mar 22 '19 at 5:05
• @danstyx answer updated – user0 Mar 22 '19 at 5:44
• @user0 good idea to use INDEX() and ROW() to hide the helper data – Andrej Adamenko Mar 22 '19 at 6:03

`=SORT(A2:A,ARRAYFORMULA(MATCH(A2:A,C2:C,0)),1)`