1

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.

Improve this question
2
  • 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
    Commented Mar 22, 2019 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
    Commented Mar 22, 2019 at 5:03

4 Answers 4

Reset to default
2

  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

Improve this answer
5
  • 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
    Commented Mar 22, 2019 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
    Commented Mar 22, 2019 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
    Commented Mar 22, 2019 at 5:36
  • support.google.com/docs/answer/…
    – Andrej Adamenko
    Commented Mar 22, 2019 at 5:45
  • Cool thank you, I got it working :D
    – danstyx
    Commented Mar 22, 2019 at 5:52
1

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.

Improve this answer
1
1 

=ARRAYFORMULA(INDEX(SORT({A2:A, IF(A2:A="", , 
 VLOOKUP(A2:A, {C2:C, ROW(C2:C)}, 2, 0))}, 2, 1), , 1))

0

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:

0

and when done you can delete column B

Improve this answer
3
  • 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
    Commented Mar 22, 2019 at 5:05
  • @danstyx answer updated
    – user0
    Commented Mar 22, 2019 at 5:44
  • 1
    @user0 good idea to use INDEX() and ROW() to hide the helper data
    – Andrej Adamenko
    Commented Mar 22, 2019 at 6:03
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.