CODEWITHSUNDEEP
arraysexcelsorting
Tim Wilcox
Tim Wilcox

Reputation: 1331

How to perform a custom sort in Excel 2010

This is how it looks prior to sorting

0, 
1 to 4,
10 to 19,
100 to 249,
1000 and up,
20 to 49,
250 to 499,
5 to 9,
50 to 99,
500 to 999,
Grand Total

I need it to look like this:

0,
1 to 4,
5 to 9,
10 to 19,

and so on..

I have seen similar questions and they use arrays. I am not familiar with arrays just yet. Any ideas?

Upvotes: 0

Views: 39

Answers (2)

David Culbreth
David Culbreth

Reputation: 2776

I'm going to answer this under the assumption that you literally have a list with the string values "0", "1 to 4", etc.

You could make a second, associated column that uses a lookup to rank the first column, to make a sort of mapping...

0 -> 0
"1 to 4" -> 1
"5 to 9" -> 2
etc.

This would be implemented in your table similar to the following:

╔═══════════╦═══════╗
║ Input     ║ Value ║
╠═══════════╬═══════╣
║ "0"       ║ 0     ║
║ "1 to 4"  ║ 1     ║
║ "5 to 9"  ║ 2     ║
╚═══════════╩═══════╝

Then you could use a VLookup to set up the column, much like Fink was saying with the "helper column". Then the values of the helper column would be fairly easily calculated with something like the following:

=VLOOKUP(text,lookup_table,2,0)

So, if your lookup table was sitting at A2:B8, and your value column was sitting in C, with the table to be sorted starting at row 10, you might end up with something like this 

=VLOOKUP($C10,$A$2:$B$8,2,0)

After setting up the VLookup "reference" column, it should be trivial to format your data as a table, and then sort by the new column that you created.

More on VLookup()
Actual VLookup reference, courtesy of Microsoft.

Upvotes: 1

Fink
Fink

Reputation: 3436

A general idea would be to define an integer based SortOrder scheme, or helper column, based on the categories and how you want to sort them. Then you could use the built in excel sort functionalities without much hassle. So for instance the 0, category would have a sort order value of 1, the 1 to 4, category would have a sort order of 2, and so forth.

Upvotes: 0

Related Questions