How to sort using a long custom list

To:  Rich

Re:  Another way

You can split up your custom sort list into multiple parts, so you can get the entire list accepted by Excel.

Do it in order, the items at the top of your custom sort list go in the first added list and the items at the bottom of the custom sort list are in the last added list.

Sort using the first added list. 

Then find where the sort ends and select the balance of the data and sort using the second list and repeat until done.

Note:  formulas are still an issue.

Forget about the "range" suggestion. It just automates importing the data. But if you had problems running out of space before, this will just automate the problem. Sorry.

How big is your "little set" with the custom sort order in it?

What exactly are you sorting in the big set? Are you sorting 1 specific (presumably the first, but not necessarily?) on a column? Are you sorting the big set "horizontally" within each row?

.

.

Actually, this article may have the solution to your problem. Rather than manually entering your custom list in the custom dialog, this article shows that you can identify the custom sort order data using a range, ie  A1:A150.

.

Excel Custom Sort with Excel VBA

https://www.thesmallman.com/excel-custom-sort-with-vba

Want to ignore some characters at the beginning of each cell when sorting? The easiest way is to simply create other cells that hold only the characters you want to use in the sorting.

.  *  Excel Manage a Custom List  (note the custom list is defined with a range)

.  *  Excel Custom Sort Using VBA

.

Code an Excel VBA sort with a custom order and a value containing commashttps://stackoverflow.com/questions/6100944/code-an-excel-vba-sort-with-a-custom-order-and-a-value-containing-commas

In VBA, Excel allows sorting values using the CustomOrder parameter to choose the sequence items are ordered. Unfortunately, the sequence of items is delimited by commas and one of my sort items contains commas. For example, I want to sort the data in the first column by the categories in the second column. The "Air, Land, or Sea" category contains commas.

.

Custom Sort VBA 

https://chandoo.org/forum/threads/custom-sort.20200/****Hi, Not really sure what is wrong with the code below but every time I run it excel application crash, I would appreciate if you figure out where ...

.

.

in VBA, maybe you can use the "Dictionary" feature to sort this data:

Collection And Dictionary Procedures

http://www.cpearson.com/excel/CollectionsAndDictionaries.htm

The Collection object and the Dictionary object are very useful for storing groups of related data.  All else being equal, I use a Dictionary object rather than a Collection object because you have access (read, write, change) to the Key property associated with an Item in the Dictionary. In a rather poor object design, the Key of an item in a Collection is write-only. You can assign a Key to an Item when you add the Item to the Collection, but you cannot retrieve the Key associated with an Item nor can you determine (directly) whether a key exists in a Collection. Dictionaries are much friendly and open with their keys. Dictionaries are also considerably faster than Collections.

         SortCollection                       SortDictionary

.

The Ultimate Guide To Collections in Excel VBAhttps://excelmacromastery.com/excel-vba-collections/

Collections are a very important part of VBA.  If you have used the language for any length of time then you will have used Collections. The most common ones are the Workbooks, Worksheets, Range and Cells collections.

Collections are similar to arrays so it is important to understand what they are and how the differ to arrays.

.

.

If none of these articles provide an answer that you can use (ie understand <g>)  provide us with an example workbook we can play with.

.

Upload Example - Trouble Shooting - Share OneDrive File

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471?tm=1523189328156

.

Includes a link to a macro to randomize text in Word.

.

**************************************

.

To:  Rich

Re:  Custom Sort

Unless your formulas references cells in the same row as the formula, the formulas will change during a sort.

When using VBA to do the sort, a VBA custom list sort uses one of the pre-established lists.

It should be possible to have VBA stack the rows in the preferred order, but that won't eliminate the formula issue.

Make sure you have a backup of the original data and sort list.

'---

Special Sort Excel add-in (20+ new ways to sort)

Download from MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents