Sort according to custom list

U

urimagic

New Member
Joined
Jun 1, 2023
Messages
16
Office Version
  1. 2013
Platform
  1. Windows
Hi, my first time here. First of all, I would like to point out that I posted this problem with Excel help...The link is here..Sort according to custom list

I did get help but the code I was requesting did not work on my pc....only to find out a bit later I had by chance indicated I was using Excel 2013. That was a huge mistake...my other pc has Excel 2013, but the pc is malfunctioning, therefore me using this one....It has Excel 2007 installed. I replied back but I have not received response....So I'm trying here. I have downloaded and installed XL2BB, but I'm not able to work it...I'll be sure to check it out and hopefully next time I will be able to use it.
Sort.png

Okay, In column A is data going in. Column C is the sort list I want column A to automatically sort by. So as I enter data into column A, the data must sort automatically to look like the data set in column E. Column G represents the updated list once I had added for example, 1 Pear 2 in column A. Thanks for all the help and assistance. Appreciated.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Please provide a copy of your data using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Dropbox, Google Drive or similar file sharing platform. You can't copy from an image, and few would be willing to manually type out all that data to test solutions.
 
Upvote 0
See if this works for you.
VBA Code: 
Option Explicit
Sub urimagic()
    Dim ws As Worksheet, rng As Range
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A2", ws.Cells(Rows.Count, "A").End(xlUp))
  
    Application.AddCustomList _
    ListArray:=Application.Transpose(ws.Range("C2", ws.Cells(Rows.Count, "C").End(xlUp)))
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=ws.Range("A2"), _
        CustomOrder:=Application.CustomListCount
        .SetRange rng
        .Apply
    End With
End Sub
 
Upvote 0
I see you wanted this to happen automatically. Try the following worksheet change code for sheet 1.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Dim rng As Range
        Set rng = Range("A2", Cells(Rows.Count, "A").End(xlUp))
        
        Application.AddCustomList _
        ListArray:=Application.Transpose(Range("C2", Cells(Rows.Count, "C").End(xlUp)))
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("A2"), _
            CustomOrder:=Application.CustomListCount
            .SetRange rng
            .Apply
        End With
        Application.EnableEvents = True
    End If
End Sub
 
Upvote 0
kevin9999 said:
I see you wanted this to happen automatically. Try the following worksheet change code for sheet 1.
VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Application.EnableEvents = False
        Dim rng As Range
        Set rng = Range("A2", Cells(Rows.Count, "A").End(xlUp))
       
        Application.AddCustomList _
        ListArray:=Application.Transpose(Range("C2", Cells(Rows.Count, "C").End(xlUp)))
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=Range("A2"), _
            CustomOrder:=Application.CustomListCount
            .SetRange rng
            .Apply
        End With
        Application.EnableEvents = True
    End If
End Sub
Click to expand...
Hi kevin9999,
I have managed to upload the file using XL2BB...Here it is..Both codes do not work...Thank you.

Excel Sort.xlsx
ABCDEFG
1ListSort ListNew ListNew List 2
21 Apple 11 Orange 11 Orange 21 Orange 2
32 Pear 11 Orange 22 Orange 22 Orange 2
41 Orange 22 Orange 11 Grape 21 Grape 2
52 Orange 22 Orange 22 Grape 22 Grape 2
61 Banana 21 Grape 1Apple 1Apple 1
71 Lemon 21 Grape 2Apple 2Apple 2
82 Grape 22 Grape 11 Pear 11 Pear 1
91 Grape 22 Grape 22 Pear 11 Pear 2
102 Apple 1Apple 12 Pear 22 Pear 1
111 Pear 1Apple 21 Lemon 22 Pear 2
122 Pear 21 Pear 11 Banana 21 Lemon 2
131 Apple 21 Pear 21 Banana 2
142 Apple 22 Pear 1ADD:
152 Pear 21 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1
 
Upvote 0
I note your image in post #1 doesn't match your XL2BB post - in post #1 your "New List" is the same length as your "List", whereas in your XL2BB sample your "New List" is 2 cells shorter. In any event, when I run the code in post #3, your sample data goes from this:
Excel Sort.xlsm
ABC
1ListSort List
21 Apple 11 Orange 1
32 Pear 11 Orange 2
41 Orange 22 Orange 1
52 Orange 22 Orange 2
61 Banana 21 Grape 1
71 Lemon 21 Grape 2
82 Grape 22 Grape 1
91 Grape 22 Grape 2
102 Apple 1Apple 1
111 Pear 1Apple 2
122 Pear 21 Pear 1
131 Apple 21 Pear 2
142 Apple 22 Pear 1
152 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


To this:
Excel Sort.xlsm
ABC
1ListSort List
21 Orange 21 Orange 1
32 Orange 21 Orange 2
41 Grape 22 Orange 1
52 Grape 22 Orange 2
61 Pear 11 Grape 1
72 Pear 11 Grape 2
82 Pear 22 Grape 1
91 Lemon 22 Grape 2
101 Banana 2Apple 1
111 Apple 1Apple 2
121 Apple 21 Pear 1
132 Apple 11 Pear 2
142 Apple 22 Pear 1
152 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


and when I add "1 Pear 2" to the bottom of the List in column A & run it again, I get this:
Excel Sort.xlsm
ABC
1ListSort List
21 Orange 21 Orange 1
32 Orange 21 Orange 2
41 Grape 22 Orange 1
52 Grape 22 Orange 2
61 Pear 11 Grape 1
71 Pear 21 Grape 2
82 Pear 12 Grape 1
92 Pear 22 Grape 2
101 Lemon 2Apple 1
111 Banana 2Apple 2
121 Apple 11 Pear 1
131 Apple 21 Pear 2
142 Apple 12 Pear 1
152 Apple 22 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


So the code is definitely working. I think moving forward, the only thing I can suggest is if you share your actual file so I can try to determine why it doesn't work for you.
In the meantime, here's a link to your file (with the code added) for you to try. Let me know if it still doesn't work for you.

Excel Sort.xlsm
 
Upvote 0
kevin9999 said:
I note your image in post #1 doesn't match your XL2BB post - in post #1 your "New List" is the same length as your "List", whereas in your XL2BB sample your "New List" is 2 cells shorter. In any event, when I run the code in post #3, your sample data goes from this:
Excel Sort.xlsm
ABC
1ListSort List
21 Apple 11 Orange 1
32 Pear 11 Orange 2
41 Orange 22 Orange 1
52 Orange 22 Orange 2
61 Banana 21 Grape 1
71 Lemon 21 Grape 2
82 Grape 22 Grape 1
91 Grape 22 Grape 2
102 Apple 1Apple 1
111 Pear 1Apple 2
122 Pear 21 Pear 1
131 Apple 21 Pear 2
142 Apple 22 Pear 1
152 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


To this:
Excel Sort.xlsm
ABC
1ListSort List
21 Orange 21 Orange 1
32 Orange 21 Orange 2
41 Grape 22 Orange 1
52 Grape 22 Orange 2
61 Pear 11 Grape 1
72 Pear 11 Grape 2
82 Pear 22 Grape 1
91 Lemon 22 Grape 2
101 Banana 2Apple 1
111 Apple 1Apple 2
121 Apple 21 Pear 1
132 Apple 11 Pear 2
142 Apple 22 Pear 1
152 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


and when I add "1 Pear 2" to the bottom of the List in column A & run it again, I get this:
Excel Sort.xlsm
ABC
1ListSort List
21 Orange 21 Orange 1
32 Orange 21 Orange 2
41 Grape 22 Orange 1
52 Grape 22 Orange 2
61 Pear 11 Grape 1
71 Pear 21 Grape 2
82 Pear 12 Grape 1
92 Pear 22 Grape 2
101 Lemon 2Apple 1
111 Banana 2Apple 2
121 Apple 11 Pear 1
131 Apple 21 Pear 2
142 Apple 12 Pear 1
152 Apple 22 Pear 2
161 Lemon 1
171 Lemon 2
182 Lemon 1
192 Lemon 2
201 Banana 1
211 Banana 2
222 Banana 1
232 Banana 2
Sheet1


So the code is definitely working. I think moving forward, the only thing I can suggest is if you share your actual file so I can try to determine why it doesn't work for you.
In the meantime, here's a link to your file (with the code added) for you to try. Let me know if it still doesn't work for you.

Excel Sort.xlsm
Click to expand...
Hi kevin9999,

No, not working..the list in column A does not sort according to sort list?
 
Upvote 0
kevin9999 said:
Then can you share the actual file?
Click to expand...
Tekse sort.xlsm
ABCD
11 Joh 5:4Gen
21 Joh 5:5Exo
31 Pet 1:6Lev
41 Pet 1:7Num
51 Pet 1:8Deu
61 Pet 1:9Jos
71 Pet 3:18Rig
81 Pet 5:7Rut
91 Pet 5:81 Sam
102 Kor 5:172 Sam
112 Tim 3:51 Kon
12Efe 1:32 Kon
13Efe 2:81 Kro
14Efe 2:92 Kro
15Gal 2:20Esr
16Heb 11:1Neh
17Heb 11:6Est
18Heb 4:16Job
19Jak 1:2Psa
20Jak 1:3Spr
21Jak 1:4Pre
22Jak 1:5Hoo
23Jak 1:6Jes
24Jak 1:7Jer
25Jak 2:19Kla
26Jak 2:20Ese
27Jak 4:7Dan
28Jak 5:14Hos
29Jak 5:15Joe
30Jak 5:16Amo
31Joh 1:12Oba
32Joh 3:3Jon
33Joh 8:32Mig
34Kol 2:12-15Nah
35Num 23:19Hab
36Rom 3:23Sef
37Rom 4:20Hag
38Rom 4:21Sag
39Rom 5:12Mal
40Rom 5:18Mat
41Rom 6:23Mar
42Rom 6:6Luk
43Rom 8:5Joh
44Spr 23:7Han
45Rom
461 Kor
472 Kor
48Gal
49Efe
50Fill
51Kol
521 The
532 The
541 Tim
552 Tim
56Tit
57Fil
58Heb
59Jak
601 Pet
612 Pet
621 Joh
632 Joh
643 Joh
65Jud
66Ope
Sheet1
 
Upvote 0
Well, that explains why the Custom Sort hasn't been working for you (I'll ignore the fact that the column containing the sort list is D and not C as per all your samples & descriptions to date because you must have adjusted the code to suit, otherwise you'd have gotten an error message). I'm not sure that you understand how a Custom sort works - suggest you have a look at this: Sort data using a custom list - Microsoft Support. As it stands, there isn't a single item in your column A that matches any item in column D - therefore, the Custom sort would have had zero effect. Not sure that I can help you any further on this. Good luck & best wishes.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Is there a way to sort time according to a Broadcast Day (6am-6am)
2
Replies
18
Views
605
kokoanutt
K
N
  • Question Question
EXCEL VBA COMBO BOX/DROP DOWN LIST
Replies
1
Views
82
Peter_SSs
Peter_SSs
A
  • Question Question
Getting '#Value!' With Combination of SORT, FILTER Formula
Replies
1
Views
159
Fluff
Fluff
L
  • Question Question
Dynamic Sort Formula
Replies
2
Views
151
Fluff
Fluff
J
  • Solved
Excel VBA Row by Row Link to Launch Userform with data from given Row
Replies
6
Views
244
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,987
Messages
6,175,794
Members
452,670
Latest member
nogarth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top