Excel custom sort

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:30 PM GMT

Question

QUESTION: Prior to switching to2007 I was able to use a Custom Sort List for our monthly price list. Unfortunately,2007 limits the number of rows to 255 and my spreadsheet has over 1200 rows.

ANSWER: simply not true. There's no such limit.

---------- FOLLOW-UP ----------

QUESTION: Yes, I'm afraid there is...and I thought I could do more than 255 with earlier versions of Excel, but now that I think of it, my spreadsheets were a lot shorter then.

255 character limit image


ANSWER: Sorry - I had read the Q as if you said it would only SORT 255 rows. I'm afraid the limit for the number of entries in a custom list has been 255 for quite some time. Nothing new in 2007.

---------- FOLLOW-UP ----------

QUESTION: Actually I was hoping someone could help me do it with a Look-up table and match formula...?

Answer

If you put your entire list in the sequence you want it in some column, then you can assign a sequential number in a parallel column, then in your source data you can use a VLOOKUP to get extract this assigned number and then you can sort on that column. For example, if your 288-row long list (made up the 288) begins like this: Banana 1 Apple 2 Prune 3 Grape 4 ... and your master list is: North 10,345 Prune Saskatchewan South 4,029 Banana Alberta etc.

then in this example, column E can have =VLOOKUP(C1,Sheet1!A$1:B$288,2,FALSE) so your table would be North 10,345 Prune Saskatchewan 3 South 4,029 Banana Alberta 1 etc.

and then you sort on col E.

HTH

Advertisement

©2024 eLuminary LLC. All rights reserved.