2

I am a beginner in VBA and I tried to do a specific sort after an aplhebitical sort.

Initially all data are shuffled and I applyed a sort to sort by alphabetical order with the following code:

Range ("A1", Range("A1").End(xlDwn)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

Please find below the first column of my excel sheet after the alphabetical sort. 

   Column A
String_Atest_1
String_Btest_2
String_C60_APT
String_C60_BLD
String_C60_FUD
String_C60_TED
String_C63_APT
String_C63_BLD
String_C63_FUD
String_C63_TED
String_C64_APT
String_C64_BLD
String_C64_FUD
String_C64_TED
...

But my goal is to do an alphabetical sort with this specific order : FUD, TED, BLD, APT.

So the excepted result is:

   Column A
String_Atest_1
String_Btest_2
String_C60_FUD
String_C60_TED
String_C60_BLD
String_C60_APT
String_C63_FUD
String_C63_TED
String_C63_BLD
String_C63_APT
String_C64_FUD
String_C64_TED
String_C64_BLD
String_C64_APT
...

Someone can help me to do this please? Thanks a lot.

Royce
  • 1,557
  • 5
  • 19
  • 44
Arthur
  • 43
  • 6
  • You'll need to create a custom list I believe. – JvdV Jul 17 '19 at 07:44
  • 2
    How about assigning numbers to your keywords( FUD->1, TED->2 etc) and creating an additional column with the corresponding numbers? You could then sort your data using this column. – Stavros Jon Jul 17 '19 at 07:55

1 Answers1

1

You can do this without VBA (if you want to)

  • Create your own custom sort list: File > Options > Advanced > General: Edit Custom List > Either Import or type your values. Here is a walkthrough.
  • Select your range and in the ribbon, click Data > Sort.
  • First rule is column B A-z
  • Add a second rule for column C and under Order drop-down, select Custom List and select the list FUD, Ted, BLD, APT.
  • Choose wheather or not you are using headers (I used them in example below)
  • Confirm, and result should look like:

enter image description here

You can automate this procedure through VBA, there are examples around for example here and here.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you very much for the answer. I updated my question, indeed my strings are only on one column and not split on three columns. Results need to be in only one column. – Arthur Jul 17 '19 at 12:51