Ik ben dol op aangepaste lijsten in Excel. Ze zijn geweldig voor de vulgreep en voor het sorteren van gegevens in een andere volgorde. Aangepaste lijsten moeten 254 items toestaan. Maar om de een of andere reden komt een lezer een situatie tegen waarin Excel alleen de eerste 38 items bewaart! We zullen dit mysterie tot op de bodem uitzoeken.
Bekijk video
- Don wil sorteren op een aangepaste lijst die numeriek is!
- gaat dit werken? Het lijkt te werken!
- Maar u kunt geen numerieke cellen importeren in het dialoogvenster met de aangepaste lijst.
- Dus probeer getallen in het dialoogvenster Aangepaste lijst te typen…. Je krijgt tijdens het typen een dwaze limiet van 255 tekens.
- WTH is de limiet? 254 artikelen? Aha - 254 items, maar minder dan 2000 tekens wanneer u de onzichtbare komma tussen elk item plaatst
- Heb wat tekstberekeningen gemaakt met
=SUM(LEN())en Ctrl + Shift + Enter enLEN(TEXTJOIN(",",True,Range)) - Tijdelijke oplossing met ABS voor sortering in dit specifieke geval voor Don
- Maar de beste oplossing … wat Don moet doen:
Videotranscriptie
Leer Excel van Podcast, aflevering 2098: limieten voor aangepaste lijsten.
Dit is gewoon bizar als Don S, met Mac 2011, toch, dus we gebruiken niet eens de echte versie van Excel. We gebruiken de nep-versie van Excel en proberen te sorteren op een aangepaste lijst, maar deze accepteert alleen de eerste 38 items in de lijst. En ik weet dat dat verkeerd is, want Excel kan tot 254 items in de lijst aan, althans dat dacht ik. Oké, en Don heeft spelersnaam, aantal overwinningen en vervolgens marge, zoals hoe ver van de score toen verwijderd was.
Dus de juiste volgorde is, de perfecte score is 0 en dan meer dan 1, minder dan 1, en meer dan 2, minder dan 2, meer dan 3, minder met 3 enzovoort. En Don probeert de kolom Margin te sorteren op deze aangepaste lijst. Nu, ik heb dat nog nooit geprobeerd, maar het zou gemakkelijk moeten zijn om te doen. Dus hier Juiste volgorde: 0, 1 en dan wordt de formule minus de waarde net voor ons en dan = het getal 2 boven +1. Oké, nu ik die twee formules heb, zou ik door 201 moeten kunnen cruisen, te ver daar, maar dat is oké. En we zouden de exacte volgorde moeten hebben die we nodig hebben tot 99 en -99. Dus er is onze perfecte reeks antwoorden. Ik ga dat kopiëren, dus Ctrl + C om te kopiëren en vervolgens Plakken als waarden. Plak die waarden zo. Oké, dus ik zou in staat moeten zijn om een aangepaste lijst in te stellen die dit aankan, toch? Geen probleem.Dus we gaan naar Bestand, Opties, Geavanceerd, scrollen 83% helemaal naar beneden, kiezen Aangepaste lijsten bewerken en we zullen onze lijst importeren. Wat? Cellen zonder eenvoudige tekst werden genegeerd. U mag geen aangepaste lijst vol getallen hebben? Maar Don zegt dat dit werkt voor de eerste 38. Wat is daar aan de hand? Nou, denk hier al een tijdje niet meer over na. Ik realiseerde me dat Don niet geprobeerd moet hebben te importeren; hij moet die cijfers net in het dialoogvenster hebben getypt.Ik realiseerde me dat Don niet geprobeerd moet hebben te importeren; hij moet die cijfers net in het dialoogvenster hebben getypt.Ik realiseerde me dat Don niet geprobeerd moet hebben te importeren; hij moet die cijfers net in het dialoogvenster hebben getypt.
Dus hier is wat ik ga doen. Ik ga Ctrl + C doen om ze allemaal te kopiëren, ik ga naar Kladblok en Plak in Kladblok op deze manier Ctrl + V, en selecteer dan alles: Bewerken, Alles selecteren en Ctrl + C, kom terug naar Excel, Bestand, Opties, Geavanceerd, 83% helemaal naar beneden, Aangepaste lijsten bewerken en ik ga die lijst hier typen alsof het Ctrl + V is. Oké, en dat werkt allemaal, maar we hebben op Toevoegen geklikt, de maximale lengte voor Custom List is overschreden. Alleen de eerste 255 tekens worden opgeslagen. En als je hiernaar kijkt, creëren ze inderdaad een aangepaste lijst die alleen naar beneden gaat in 38, -38, 39 en dan BAM! De laatste 3, toch?
Dus dit is zo bizar. Ze laten me eigenlijk een aangepaste lijst met nummers maken, maar ze laten me geen 255 krijgen. Ik bedoel, het werkt. Het werkt en als we dan echt proberen het hier te sorteren; dus we zeggen Gegevens, Sorteren en sorteren op deze aangepaste lijst, degene die alleen naar 39 gaat, klik op OK, klik op OK. Als het in de lijst staat, wordt het correct gesorteerd. Oké, dus de positieve +6 wordt vóór de -6 weergegeven; maar als we eenmaal verder zijn dan 39, wordt het gewoon gesorteerd in de reeksen die niet in de lijst staan, dus het gaat hier van klein naar groot. Iemand die 67 punten mist, is dus beter dan iemand die +42 punten mist. Het is gewoon helemaal smerig.
Oké, en wat is er aan de hand dat dit alleen naar 38 gaat? Nu, er is zeker elk ander nummer, weet u, ik denk dat het tot in de jaren 30 zal duren. We beginnen het daar leuk te vinden, toch? Of daar, een van die twee. Dus, wat is dat? Dat zijn in totaal 78 items. En hey, ik weet dat ze het toestonden - ik heb 250 forums omdat ik tijdens mijn seminars de hele tijd over klanten praat, oké? Je kunt 250 forums hebben, ik zal het je laten zien. Dus Item 1, en daar kunnen we natuurlijk het Filling Item voor gebruiken. Ik sleep zo naar 254. Dit zijn geen formules, dus we zouden Bestand, Opties, Verder moeten kunnen doen, doorgaan naar Aangepaste lijsten bewerken en we zullen die lijst importeren, oké? Daar is het, BAM! Geen problemen, geen foutmelding. Alles is geweldig, alles is - het is niet geweldig. Het gaat alleen naar item 234. Wacht, ik weet dat je 254 kunt hebben.Waarom stopt het bij 234? Dat is bizar, dat is bizar. Hoe zit het daarmee?
Dus hier weten we dat het hier slechts naar item 234 gaat. Oké, toen we de items in de lijst aan het typen waren, was er een aantal tekens. Er was een limiet. Dus ik vraag me af of er een aantal tekens is dat hier de limiet is = SUM (LEN van dat hele stel dingen, druk op CTRL + SHIFT ENTER, en het zijn 1764 tekens - 234 items. En ik weet dat je 254 kunt hebben, ik heb dit eerder gedaan.
En laten we iets gekker proberen. Oké, laten we dit proberen. Laten we het proberen in plaats van het item, laten we iets langer proberen. Dus 10 karakters een SPATIE en dan nummer 1, we gaan 254 rijen omlaag. En we zullen proberen deze lijst te importeren: dus Bestand, Opties, Geavanceerd, Aangepaste lijsten bewerken, we zullen deze lijst importeren. Geen foutmelding. Het lijkt erop dat het werkte, maar het gaat maar naar 140. Wat is daar in vredesnaam mee aan de hand? Wat is de limiet? Ik dacht dat het 254 zou kunnen zijn. Dus laten we eens kijken hoeveel tekens we hebben als we naar 140 komen. Oké, dus laten we al het andere hierna laten staan en in feite kom ik hier naar deze formule en kopieer ik exact dezelfde formule over. Oké, nee.
Op dit moment ben ik behoorlijk geïrriteerd over het Excel-team. Hoe gaat het, hier 1764 en hier 1852. Hé, Microsoft, wat is de limiet? Precies wat is de limiet? Ah, maar hier is het ding. Ze moeten dit opslaan als een reeks gescheiden strings, oké? Ze nemen dus alle items en voegen na elk item een komma toe. Oké, dus sinds we Office 365 hebben, kunnen we de nieuwe Text Join gebruiken, dus = TEXTJOIN van alle items met een komma ertussen. Ik weet niet of het echt een komma is of niet. Negeer dat, dan True, komma en deze items. Dus dat snappen we. En eigenlijk wil ik gewoon de lengte van dat hele ding weten. Dus de lengte is 1997 en als ik hier hetzelfde doe, 1991. Oh! Het is dus duidelijk dat de limiet 2000 tekens moet zijn, inclusief een onzichtbare komma tussen elk item.
Dit is allemaal behoorlijk bizar. Oké, dus ik dacht altijd dat het 254 items waren, het zijn geen 254 items. Het zijn 254 items, op voorwaarde dat het minder dan 2000 karakters is, op voorwaarde dat de items niet te lang zijn. Oké, dus om mijn theorie te testen, laten we gewoon A spatie 1 gebruiken en we pakken de vulhendel en slepen. Deze zouden heel mooi en kort moeten zijn omdat - En we gaan omlaag naar 255, 254. Laten we naar 255 gaan om het te testen.
Oké, dus nu hiermee, als ik vraag naar de lengte van de tekst Join, 1421. Helemaal geen probleem. Dus selecteer het hele ding en Bestand, Opties, Geavanceerd, scrol helemaal naar beneden, Aangepaste lijsten bewerken, klik op Importeren. Oké, en schakel helemaal uit tot 254. Oké, het zijn dus 254 items, op voorwaarde dat het minder dan 2000 tekens bevat, inclusief een onzichtbare komma na elk item, zo werkt dit.
Weet je, dus - maar terug naar Don's probleem hier. Het is zeker vervelend dat de dialoogbox, als we gewoon naar binnen gaan en dingen in de dialoogbox gaan typen in plaats van een element van 2.000 tekens, er 255 tekens op staan. Oké, dus Don kan dit ding niet typen en als we proberen om nummers te importeren, weigert het de nummers te importeren. Er staat geen deal. Alles dat geen platte tekst is, zal niet werken, oké?
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.
Nou, daar ga je. Don, een van de gekkere vragen die ik ooit heb gehoord in Excel en we hebben er minstens 2 ontdekt - Nou, zeker 1 bug in Excel die het dialoogvenster Aangepaste lijst niet meer dan 255 tekens toestaat. En dan dit bizarre ding dat ze blijkbaar een aangepaste lijst op nummers zullen sorteren, maar ze laten je geen nummers importeren. Oké, dus ik ga die bug nummer 2 bellen. En dan deze oplossing hier, oké?
Dus hey, ik wil Don bedanken voor het insturen van de vraag en als je zo lang blijft hangen, wil ik je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2098.xlsm
