Google Sheets – How to Sort Data Range Based on Another Data Range

formulasgoogle sheetsgoogle-sheets-arrayformulasortingvlookup

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:

enter image description here

Also since it wasn't in the screenshot, the sheet name is Sheet1.

Best Answer

  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.

screenshot

Related Topic