Custom List Limits

Learn Excel from MrExcel Podcast, Episode 2098: Custom List Limits.

This is just a bizarre when Don S, using Mac 2011, right, so we're not even using the real version of Excel.  We're using the fake version of Excel, trying to sort by a Custom List but it's only accepting the first 38 items in the list.  And I know that's wrong because Excel can handle up to 254 items in the list or at least that's what I thought.  Alright, and Don has Player Name, # of Wins and then Margin, like how far off from the score were then. 

So the proper sequence is, the perfect score is 0 and then over by 1, under by 1, and over by 2, under by 2, over by 3, under by 3 and so on.  And Don is trying to sort the Margin column by this Custom List.  Now, I've never tried that but, hey, it should be easy to do.  So over here Proper Sequence: 0, 1 and then the formula is going to be minus the value just before us and then = the number 2 above +1.  Alright, now that I have those two formulas I should be able to cruise down through 201, too far there but that's okay.  And we should have the exact sequence that we need down to 99 and -99.  So there's our perfect set of answers.  I'm going to copy that so Ctrl+C to copy and then Paste as Values.  Paste those values like that.  Alright, so I should be able to set up a Custom List that will handle this, right?  No problem.  So we go to File, Options, Advanced, scroll 83% all the way down, choose Edit Custom Lists and we will Import our list.  What?  Cells without simple text were ignored.  You're not allowed to have a Custom List full of numbers?  But Don says this is working for the first 38.  What's up with that?  Well, haven’t think about this for a while.  I realized that Don must not have been trying to import; he must have just been typing those numbers into the Dialog Box. 

So here's what I’m going to do.  I'm going to do Ctrl+C to copy all those, I'm going to go to Notepad and Paste in Notepad like this Ctrl+V, and then select everything: Edit, Select all, and Ctrl+C, come back to Excel, File, Options, Advanced, 83% all the way down, Edit Custom Lists and I'm going to type that list in here like it's Ctrl+V.  Alright, and that all works but we clicked Add, the maximum length for Custom List has been exceeded.  Only the first 255 characters will be saved.  And when you look at this, sure enough, they are creating a Custom List that only goes down into 38, -38, 39 and then BAM!  The last 3, right?

So, this is so bizarre.  They're actually letting me create a Custom List with numbers but they're not allowing me to get 255.  I mean, it works.  It works and then if we actually try and sort over here; so we'll say Data, Sort and sort on this Custom List, the one that only goes to 39 click OK, click OK.  Well, if it's in the list it sorts correctly.  Alright,  so the positive +6 show before the -6; but then once we get down to anything beyond 39 it's just going to sort in the sequences that's not in the list, so it's going from smallest to largest here.  So, somebody missed by 67 points is better than somebody missed by +42 points.  It is just completely screwy. 

Alright, and what's up with this only going to 38?  Now, sure there's every other number so, you know, I guess it's going to down into the 30s.  We're getting down to like there, right?  Or there, one of those two.  So, what is that?  That is a total of 78 items.  And hey, I know they allowed - have 250 forums because I talk about customers all the time in my seminars, alright?  You can have 250 forums, let me show you.   So Item 1, and of course we can use the Filling Item for that.  I'll drag down to 254, like that.  Now those aren't formulas so we should be able to do File, Options, Advance, go on to Edit Custom Lists and we will Import that list, alright?  There it is, BAM!  No problems, no error message.  Everything is great, everything is – It’s not great.  It only goes to Item 234.  Wait, I know you can have 254.  Why is it stopping at 234?  That's bizarre, that's bizarre.  What's up with that? 

So here, we know that it's only going down to item 234 right there.  Okay now, when we were typing the Items in the list there's some number of characters.  There was the limit.  So I wonder if there's some number of characters that's the limit here =SUM(LEN of that whole bunch of stuff, press CTRL+SHIFT ENTER, and it’s 1764 characters - 234 items.  And I know that you can have 254, I've done this before. 

And let's try something crazier.  Alright, let's try this.  Let's try instead of item let's try something longer.  So 10 characters a SPACE and then number 1, we’ll go down 254 rows.  And we'll try and import this list: so File, Options, Advanced, Edit Custom Lists, we’ll Import this list.  No error message.  Seems like it worked but it only goes down to 140.  What the heck is up with that?  What is the limit?  I thought it could be 254.  So let's see, how many characters we have if we get down to 140.  Alright, so let's leave everything else after this and in fact I'll come over here to this formula and copy the exact same formula over.  Alright, no. 

At this point, I'm pretty exasperated with the Excel team.  What's up, here 1764 and here 1852.  Hey, Microsoft, what is the limit?  Exactly what is the limit?  Ah, but here's the thing.  They must be storing this as a series of delimited strings, alright?  So they're taking all the Items and then they're adding a comma after each one.  Alright, so here since we have Office 365, we can use the new Text Join, so =TEXTJOIN of all of those with a comma in between.  I don’t know if it’s really a comma or not.  Ignore that, then True, comma  and these items.  So, we get that.  And actually I just want to know the length to that whole thing.  So the length is 1997 and when I do the same thing over here, 1991.  Oh!  So, clearly the limit must be 2000 characters including an invisible comma between each item. 

This is all pretty bizarre.  Alright, so I always thought it was 254 Items, it's not 254 Items.  It’s 254 Items, provided it’s less than 2,000 characters, provided the Items aren't too long.  Alright, so just to test my theory, let's just use A space 1 like that and we’ll grab the Fill Handle and drag.  These should be really nice and short because - And we'll go down to 255, 254.  Let's go to 255 to test it. 

Alright, so now with this, if I ask for the Length of the Text Join, 1421.  No problem at all. So select the whole thing and File, Options, Advanced, scroll all the way down to the bottom, Edit Custom Lists, click Import.  Alright, and turn off all the way down to 254.  Alright, so it's 254 Items provided it’s less than 2,000 characters including a invisible comma after each item is how this works. 

You know, so - but back to Don’s problem here.  It sure is annoying that the Dialog Box, if we just go in and start typing things in the Dialog Box instead of having a 2,000 character element, it has a 255 character on it.  Alright, so Don has no way to type this thing in and when we try and Import numbers, it refuses to Import the numbers.  It says no deal.  Anything that's not plain text is not going to work, alright?   

So, the one thing I suggested to Don is an alternate solution.  I say, hey, let's just come out here and add a Helper Column and this Helper Column is going to be the – if the Absolute Value of that number, alright.  And we'll double click to shoot that down and then what you’re going to do is you're just going to sort Descending by the Absolute Value, a setting by the Absolute Value, alright.  And then the 4, 6 and then -6, alright, these are all just get sorted together, you know.  So it's not bad, I guess what you could really do is you could sort by the Helper and then Add a Level and then Sort by the Margin,  descending Largest to Smallest, click OK and that will get what Don is looking for.  So, all of the +6 will show up before the -6, and then 8 and then -11, alright.  You know, but this is a hassle.  Like, hey, Microsoft.  Why do we have to go to all this hassle?  Why would you let us type 2,000 characters into the Dialog Box or even better yet, since it apparently works to have numbers in the Custom List, I mean it is working here, why won't you let us import it? 

Alright now, here is the workaround.  And I'm sorry that took this freaking long to get to the end but this is what I had to go through to figure all this out.  So, I'm going to add this Workaround Column, alright.  And this Workaround Column is going to be the Text, the text of that number in “”, or “0”) like that.  And we're going to copy that down.  Alright, so now we're taking these numbers here - see the numbers are right justified and we are left justifying it by using the Text Function.  Unfortunately though, you can't use formulas in the Custom List.  If I would come here to File, Options, Advanced, go down to Edit Custom Lists and we'll Import that – no!  Cells without simple texts were ignored.  Okay, fine.  Getting really annoyed now, right? 

Now, what I'm going to do is I want to select the Workaround Column.  I'm going to use the Home tab and I'm going to copy it and then I'm going to paste and paste Values.  If you don't do this step, it's not going to work.  And here's how to prove to yourself that it works.  Alright, if those are really numbers, when you do the SUM function it's going to give you an answer like this.  That's going to be 5, alright?  But if I do the SUM of the Workaround Column, I'm going to get O, alright.  And the reason is, is the SUM can't sum texts.  Alright, so these are real numbers, these are real texts.  You have to get it to be real text.  Once you have it as real text, select the whole thing.  Go to File, Options, Advanced, Edit Custom Lists.  We'll get rid of this old Custom List here that we don't need.  We'll get rid of this list that we don't need.  We'll get rid of this list that we don't need.  We'll even get rid of this list that only goes down to 38 and 39; and then finally, we'll Import the whole list.  And these whole lists goes from 0 all the way down to 99 and -99 and presumably store it as text, alright?  But here's the cool, awesome, amazing thing.  Let's just come back up and we'll sort this data by name: Data, A-Z, and now everything is just completely random there.  And then, we will Sort by Margin and the order is going to be a Custom List.  The custom list is going to be this sequence, click OK and click OK.  And it works: the 4, the 6s, the -6, the 8, the -11 and so on. 

Now, the beautiful thing about this is once you get it set up, alright, it's a hassle to put these numbers over here in the- off to the side, change them to text, Copy, Paste Special Values and then Import that list.  But the beautiful thing is, once that's imported then forevermore on this computer we're going to be able to sort correctly by margin.  So Don, geez! I think that it's worth it to go through that whole thing.

617 Excel mystery solved.  Now, this is a new mystery why this Custom List wouldn't work but this book is full of stuff like this video, just the bizarre, the arcane and so on.  Click that “i” on the top right-hand corner to buy the book.

Alright, Don wants to sort by Custom List that is numeric, will it work?  It seems to work.  It seems to work for the first 38 numbers but you can't import numeric cells into the Custom List Dialog Box.  Ah, so try and type the custom numbers or actually, I guess I pasted to Notepad and then from Notepad I paste it.  But then there's this crazy 255 character limit when typing.  That must be left over from Excel 2003, right?  Because the limit is more than that now.  You can have 254 items.  Is it 254 items?  Ah, it’s 254 items less than 2000 characters when you have the invisible column, comma between each item. 

Now we did some Math here, text math with =SUM(LEN) then CTRL+SHIFT ENTER and then the LEN(TEXTJOIN).  If you have Office 365 and are on the latest version - even came up with this workaround with the Absolute Value for sorting which will help in this particular case for Don.  But I think what Don needs to do, the best workaround, and this is what I tell them to do on the YouTube video.  Put those numbers: 0, 1, -1, in A1:A200.  And then in the column next to that, =TEXT(A1, “0”).  That's going to be converted to text.  Copy that formula all the way down and then select that range of formulas Copy and then after you Copy go to the Paste dropdown.  So Copy, Paste and Paste as Values.  And if you're successful, when you do the SUM of that whole thing, it’s going to sum to a number that's not or it’s going to sum to 0.  It's going to sum to 0.  Be careful.  Don't choose all of your numbers because your numbers do happen to sum to 0.  Now the B contains text numbers you can import because of Custom Lists.

Well, there you go.  Don, one of the crazier questions I've ever heard in Excel and we've uncovered at least 2 – Well, definitely 1 bug in Excel that the Custom List Dialog Box won't allow more than 255 characters.  And then this bizarre thing that the apparently will sort a Custom List by numbers but they won't let you import numbers.  Alright, so I'm going to call that bug number 2.  And then this workaround here, alright?

So hey, I want to thank Don for sending the question in and if you hang around this long, I want to thank you for stopping by.  We’ll see you next time for another netcast from MrExcel.