Jag älskar anpassade listor i Excel. De är utmärkta för fyllningshandtaget och för att sortera data i en annan sekvens. Anpassade listor bör tillåta 254 objekt. Men av någon anledning stöter en läsare på en situation där Excel bara behåller de första 38 objekten! Vi kommer till botten av detta mysterium.
Kolla på video
- Don vill sortera efter en anpassad lista som är numerisk!
- Kommer det att fungera? Det verkar fungera!
- Men du kan inte importera numeriska celler till den anpassade listdialogrutan.
- Så försök att skriva siffror i dialogrutan Anpassad lista …. Du slås med en dum 255 teckenbegränsning när du skriver.
- WTH är gränsen? 254 artiklar? Aha - 254 objekt, men mindre än 2000 tecken när du lägger till det osynliga komma mellan varje objekt
- Gjorde lite textmatematik med
=SUM(LEN())och Ctrl + Skift + Enter ochLEN(TEXTJOIN(",",True,Range)) - Lösning med ABS för sortering i detta fall för Don
- Men den bästa lösningen … det som Don behöver göra:
Videoutskrift
Lär dig Excel från Podcast, avsnitt 2098: Anpassade listgränser.
Det här är bara bisarrt när Don S använder Mac 2011, så vi använder inte ens den verkliga versionen av Excel. Vi använder den falska versionen av Excel och försöker sortera efter en anpassad lista, men den accepterar bara de första 38 objekten i listan. Och jag vet att det är fel eftersom Excel kan hantera upp till 254 objekt i listan eller åtminstone det är vad jag trodde. Okej, och Don har spelarnamn, # vinster och sedan marginal, som hur långt ifrån poängen var då.
Så den korrekta sekvensen är, den perfekta poängen är 0 och sedan över med 1, under av 1, och över av 2, under av 2, över av 3, under av 3 och så vidare. Och Don försöker sortera marginalkolumnen efter den här anpassade listan. Nu har jag aldrig provat det men hej, det borde vara lätt att göra. Så här kommer ordentlig sekvens: 0, 1 och då kommer formeln att vara minus värdet precis före oss och sedan = siffran 2 ovanför +1. Okej, nu när jag har dessa två formler borde jag kunna kryssa ner genom 201, för långt där men det är okej. Och vi borde ha den exakta sekvensen som vi behöver ner till 99 och -99. Så det finns vår perfekta uppsättning svar. Jag ska kopiera det så Ctrl + C för att kopiera och sedan klistra in som värden. Klistra in dessa värden så. Okej, så jag borde kunna skapa en anpassad lista som hanterar detta, eller hur? Inga problem.Så vi går till Arkiv, Alternativ, Avancerat, rullar 83% hela vägen ner, väljer Redigera anpassade listor och vi importerar vår lista. Vad? Celler utan enkel text ignorerades. Du får inte ha en anpassad lista full av nummer? Men Don säger att detta fungerar de första 38. Vad är det med det? Jag har inte tänkt på det på ett tag. Jag insåg att Don inte måste ha försökt importera; han måste precis ha skrivit in dessa siffror i dialogrutan.Jag insåg att Don inte måste ha försökt importera; han måste precis ha skrivit in dessa siffror i dialogrutan.Jag insåg att Don inte måste ha försökt importera; han måste precis ha skrivit in dessa siffror i dialogrutan.
Så här är vad jag ska göra. Jag ska göra Ctrl + C för att kopiera alla dessa, jag ska gå till Anteckningar och klistra in i Anteckningar så här Ctrl + V, och välj sedan allt: Redigera, Markera alla och Ctrl + C, kom tillbaka till Excel, Arkiv, Alternativ, Avancerat, 83% hela vägen ner, Redigera anpassade listor och jag ska skriva den listan här som om den är Ctrl + V. Okej, och att allt fungerar men vi klickade på Lägg till, den maximala längden för anpassad lista har överskridits. Endast de 255 första tecknen sparas. Och när du tittar på detta skapar de nog en anpassad lista som bara går ner i 38, -38, 39 och sedan BAM! De sista 3, eller hur?
Så det här är så bisarrt. De låter mig faktiskt skapa en anpassad lista med siffror men de tillåter inte att jag får 255. Jag menar, det fungerar. Det fungerar och sedan om vi faktiskt försöker sortera här; så vi säger Data, Sortera och sortera i den här anpassade listan, den som bara går till 39, klicka på OK, klicka på OK. Om det finns i listan sorterar det rätt. Okej, så den positiva +6-showen före -6; men när vi väl har nått något över 39 kommer det bara att sorteras i de sekvenser som inte finns i listan, så det går från minsta till största här. Så, någon som har missat 67 poäng är bättre än någon som missar +42 poäng. Det är helt skruvigt.
Okej, och vad händer med att det här bara går till 38? Nu är det säkert alla andra nummer så du vet, jag antar att det kommer att gå ner på 30-talet. Vi håller på att gilla där, eller hur? Eller där, en av dessa två. Så, vad är det? Det är totalt 78 artiklar. Och hej, jag vet att de har tillåtit - ha 250 forum eftersom jag pratar om kunder hela tiden i mina seminarier, okej? Du kan ha 250 forum, låt mig visa dig. Så artikel 1, och naturligtvis kan vi använda fyllningsartikeln för det. Jag drar ner till 254, så. Nu är det inte formler så vi borde kunna göra File, Options, Advance, gå vidare till Redigera anpassade listor så importerar vi listan, okej? Där är det, BAM! Inga problem, inget felmeddelande. Allt är bra, allt är - Det är inte bra. Det går bara till artikel 234. Vänta, jag vet att du kan ha 254.Varför stannar det vid 234? Det är bisarrt, det är bisarrt. Vad är det med det?
Så här vet vi att det bara går ner till artikel 234 just där. Okej nu, när vi skrev artiklarna i listan finns det ett antal tecken. Det var gränsen. Så jag undrar om det finns ett visst antal tecken som är gränsen här = SUMMA (LÄNG av det hela gänget, tryck CTRL + SKIFT ENTER, och det är 1764 tecken - 234 objekt. Och jag vet att du kan ha 254, jag har gjort detta tidigare.
Och låt oss prova något galnare. Okej, låt oss prova det här. Låt oss prova istället för objektet låt oss prova något längre. Så tio tecken ett mellanslag och sedan nummer 1, vi går ner 254 rader. Och vi försöker importera den här listan: så Arkiv, Alternativ, Avancerat, Redigera anpassade listor, vi importerar den här listan. Inget felmeddelande. Verkar som om det fungerade men det går bara ner till 140. Vad är det med? Vad är gränsen? Jag trodde att det kunde vara 254. Så låt oss se, hur många tecken vi har om vi kommer ner till 140. Okej, så låt oss lämna allt annat efter detta och faktiskt kommer jag hit till denna formel och kopierar exakt samma formel över. Okej, nej.
Vid denna tidpunkt är jag ganska upprörd över Excel-teamet. Vad händer här 1764 och här 1852. Hej, Microsoft, vad är gränsen? Exakt vad är gränsen? Ah, men här är saken. De måste lagra detta som en serie avgränsade strängar, okej? Så de tar alla artiklar och sedan lägger de till ett komma efter var och en. Okej, så här eftersom vi har Office 365 kan vi använda den nya Text Join, så = TEXTJOIN av alla med kommatecken mellan. Jag vet inte om det verkligen är ett komma eller inte. Ignorera det, sedan True, komma och dessa objekt. Så vi förstår det. Och faktiskt vill jag bara veta längden på det hela. Så längden är 1997 och när jag gör samma sak här, 1991. Åh! Så klart måste gränsen vara 2000 tecken inklusive ett osynligt komma mellan varje objekt.
Det här är ganska bisarrt. Okej, så jag har alltid trott att det var 254 artiklar, det är inte 254 artiklar. Det är 254 objekt, förutsatt att det är mindre än 2000 tecken, förutsatt att artiklarna inte är för långa. Okej, så bara för att testa min teori, låt oss bara använda ett mellanslag 1 så tar vi tag i Fill Handle och drar. Dessa borde vara riktigt trevliga och korta för - Och vi går ner till 255, 254. Låt oss gå till 255 för att testa det.
Okej, så nu med det här, om jag ber om längden på textanslutningen, 1421. Inget problem alls. Så välj det hela och Arkiv, Alternativ, Avancerat, bläddra hela vägen ner till botten, Redigera anpassade listor, klicka på Importera. Okej och stäng av hela vägen ner till 254. Okej, så det är 254 objekt förutsatt att det är mindre än 2000 tecken inklusive ett osynligt komma efter varje objekt är hur det fungerar.
Du vet, så - men tillbaka till Dons problem här. Det är säkert irriterande att dialogrutan, om vi bara går in och börjar skriva saker i dialogrutan istället för att ha ett 2000-teckenelement, har den 255 tecken på sig. Okej, så Don har inget sätt att skriva in den här saken och när vi försöker importera nummer vägrar den att importera siffrorna. Det står inget avtal. Allt som inte är vanlig text kommer inte att fungera, okej?
Så det enda jag föreslog för Don är en alternativ lösning. Jag säger, hej, låt oss bara komma ut här och lägga till en hjälpkolumn och den här hjälpkolumnen kommer att bli - om det absoluta värdet på det numret, okej. Och vi dubbelklickar för att skjuta ner det och vad du ska göra är att du bara ska sortera Descending by the Absolute Value, en inställning av Absolut Value, okej. Och sedan är 4, 6 och sedan -6, okej, alla sorteras bara, vet du. Så det är inte dåligt, jag antar att det du verkligen kan göra är att du kan sortera efter hjälparen och sedan lägga till en nivå och sedan sortera efter marginalen, fallande Största till minsta, klicka på OK så får du vad Don letar efter. Så, alla +6 kommer att visas före -6, och sedan 8 och sedan -11, okej. Du vet, men det här är ett besvär. Som, hej, Microsoft.Varför måste vi gå till allt detta besvär? Varför skulle du låta oss skriva 2000 tecken i dialogrutan eller ännu bättre, eftersom det tydligen fungerar att ha nummer i den anpassade listan, jag menar att det fungerar här, varför låter vi oss inte importera det?
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.
Tja, där går du. Don, en av de galnare frågorna jag någonsin har hört i Excel och vi har upptäckt minst 2 - Tja, definitivt 1 bugg i Excel att dialogrutan Anpassad lista inte tillåter mer än 255 tecken. Och sedan den bisarra saken som tydligen kommer att sortera en anpassad lista efter nummer men de låter dig inte importera nummer. Okej, så jag kommer att ringa det fel nummer 2. Och sedan den här lösningen här, okej?
Så hej, jag vill tacka Don för att ha skickat in frågan och om du hänger så länge vill jag tacka dig för att du kom in. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2098.xlsm
