Custom Sort VERY Long Sort Order

[kgaul01]

Hi All, I have a spreadsheet I am writing vba for and I am stumped on my custom sort. I am trying to sort by account name (column E) in a specific order. However, the list of accounts is VERY large so I have been thinking of maybe a helper column referencing another table? A concern I have with the helper is that having that many more formulas will slow the workbook down. I look forward to your suggestions and thank you in advance!

 

[shg]

so I have been thinking of maybe a helper column

That's what I would do.

 

[kgaul01]

Unfortunately I don't fully understand how I would implement the helper column. I can make a table with the lookups and the sort order but not sure how to best set it up. Any guidance?

 

[shg]

List the sort values somewhere in sort order, and in the helper column, use a MATCH function to get the sort order of the column you're sorting by.

If the list is so long that you don't want to do a linear search, use a two-column table with the sort items in alphabetical order, the sort order in the next column, and use a VLOOKUP formula in the helper column.

 

[kgaul01]

I only know how to use match in vba to return a range value. (Sorry, vba novice) Could you help with sample code?

 

[shg]

I'm not talking about VBA, I'm talking about a helper column with a formula in the table of data you want to sort.

 

[kgaul01]

Oh ok. So what would my formula look like?

 

[shg]

For a single-column list,

=match(valueFromMySortColumn, mySortList, 0)

Then sort on the helper column.

 

[kgaul01]

Thanks for the help but I actually ended up using a clock up in a similar way. (Just needed the "like" search)