Kan Excel VLOOKUP returnera alla resultat och ansluta dem med ett kommatecken däremellan?
Kolla på video
- Målet är att sammanfoga alla svaren från en VLOOKUP
- Bills metod: Använd en VBA-funktion som heter GetAll
- Unik lista med Ta bort dubbletter
- Mike's metod:
- Unik lista med Advanced Filter
- TEXTJOIN-funktionen har lagts till i Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- På grund av IF-funktionen kräver formeln Ctrl + Shift + Enter när du redigerar formeln
- Alt AQOR Enter kör igen Advanced Filter!
Videoutskrift
Avsnitt 183: Gå med i alla VLOOKUP-matchningar
Bill Jelen: Hej, välkommen tillbaka. Det är dags för ytterligare en Duelling Excel Podcast. Jag är Bill Jelen från, jag får sällskap av Mike Girvin för Excel är kul. Detta är vår avsnitt 183: Gå med i alla VLOOKUP-matcher.
(Musik)
Okej, dagens fråga från Matt. Kan VLOOKUP returnera alla resultat och sammanfoga dem med ett kommatecken mellan varje. Till exempel kan 109876, som är dessa två här, returnera kommat utrymme med låg olja Kontrollerat 12/12. Och naturligtvis, om det fanns fler, skulle det återvända mer. Okej, så min lösning här kommer att använda lite VBA. Okej, så se till att den sparas som xlsm eller så kan du inte köra VBA eller xlsb, men inte xlsx - xlsx är den filen som inte kan köra VBA. Vi kommer att trycka på Alt + F11, se till att du är på Dual183 eller vad arbetsboken heter. Sätt in modulen i den tomma modulen så klistrar vi in den här koden, okej.
Låt oss ta en titt på den här funktionen GetAll, och här är ID-numret som vi letar efter och sedan det intervall som vi vill se. Och vi börjar, vi ska returnera en variabel som heter GetAll, så vi börjar med att det är lika med tomt tomt. För varje cell i mitt sortiment, om cellvärdet är vad vi letar efter, tar vi GetAll = GetAll & “” och sedan Cell.Offset (0 rader, 1 kolumn), med andra ord värdet det är precis bredvid det ID-numret, för tillbaka i VBA, här är ID-numret. Om vi hittar matchande ID-nummer vill vi gå 1 kolumn över. Tänk nu om du vill gå 2 kolumner över eller 3 kolumner, ja då ändrar du de här 0 raderna och 1 kolumnen till att vara en 2. Okej, kolla också om - vi lägger inte ett kommatecken om det här är den första.Så om GetAll-variabeln för närvarande är ””, lägger vi inte kommautrymmet, okej?
Så nu när vi har den här funktionen här, se hur lätt det är att lösa Matts problem. Vi kommer hit och låt oss ta hans ID, Ctrl + C och klistra in Ctrl + V så. Data, ta bort dubbletter, klicka på OK. Så det finns en unik lista med ID och sedan vill vi säga = getall och vi letar efter det värdet i E2-komma. Om jag tittar igenom detta intervall här trycker jag på F4. F4 fungerar precis som en vanlig funktion. Och flytta igen Matts fråga ur vägen, dubbelklicka för att skjuta ner den. Det kommer att fungera.
Och låt oss bara försöka, låt oss prova något galet här. Låt oss göra en fras 1 och lägga bara en massa av dem som fras 1 till 10. Vi signerar alla dessa till 109999. Klistra in och klistra sedan in här. Kopiera den formeln ner, redigera formeln så att den går helt till botten, naturligtvis. Japp. Och det kommer att returnera alla dessa fraser. Okej, så det är min lösning, VBA, en liten funktion där. Mike, låt oss se vad du har.
Mike Girvin: Tack. GetAll, det är en fantastisk VBA-funktion. Okej, jag ska gå över till arket här. Jag har redan konverterat den till en Excel-tabell så att vi förhoppningsvis kommer att uppdateras när vi lägger till poster nedan.
Nu det första jag ska göra i två delar. Jag kan göra en formel här för att extrahera en unik lista men jag vill titta på ett annat alternativ: Avancerat filter har ett unikt listalternativ för extrahering och det kan uppdateras. Jag kommer att markera bara ID-kolumndata, över till Advanced Filter eller jag ska använda tangentbordet Alt, A, Q. Nu, Filterlista på plats, inget sätt. Jag vill kopiera den till en annan plats. Det fick bara A-kolumnen och eftersom det är en Excel-tabell som kommer att expandera senare. Jag har inga kriterier, jag vill bara kopiera den till D1 och bara kontrollera Unika poster. Klicka på OK.
Nu ska jag komma hit, Alla kommentarer kommer in och jag ska använda en funktion som bara fungerar i Excel 2016 Office 365: = TEXTJOIN-funktion. Denna funktion ensam är värt att få den senaste versionen av Excel. Det här är en så vanlig uppgift som folk vill göra, gå med många saker tillsammans. Nu är vår avgränsare i “,”, och det fantastiska med den här funktionen är att vi kan berätta det för att ignorera tomma celler. Nu kan jag sätta SANT, 1 eller Lämna det, utelämna det. Så jag ska lämna det, utelämna det. Och här behöver vi vår text. Vi ska använda IF-funktionen för att filtrera bort och få precis de föremål vi vill ha. Jag ska säga titta igenom hela denna kolumn här: Tabellnamn och sedan i () fältnamnet, är någon av er = till denna relativa cellreferens, det är det logiska testet. Om jag skulle klicka på detta och trycka på F9-tangenten för att utvärdera,du kunde se just nu har vi bara 2 TRUES, Ctrl + Z nu skriver jag ett kommatecken och med utbudet av Trues och Falses, nu kan jag ge det artiklarna att välja ut. Så nu väljer vi bara de föremål som har SANT här från detta sortiment. Comma och jag vill se till att sätta “” - det kommer att visas som en tom cell när det gäller det andra argumentet i TEXTJOIN.
Nu kommer jag att stänga parentes och nu kommer IF-funktionen att skapa den strängen Trues och Falses, de faktiska objekten från detta intervall kommer att plockas upp om den ser det sant och alla andra objekt kommer att ha den tomma cellen. Och gissa vad? TEXTJOIN ignorerar helt och hållet alla dessa tomma celler och returnerar bara de objekt som matchar detta ID och går sedan med det med den avgränsaren. Nu är detta definitivt en matrisformel som kräver den speciella tangenttryckningen Ctrol + Skift + Enter. Det logiska testargumentet innehåller vår Array-operation och det argumentet kan inte beräkna denna Array-operation korrekt om vi inte använder tangentbordet Ctrl + Shift + Enter. Nu ska jag stänga parenteser. Egentligen kan vi bevisa 1 här i Text 1 om jag F9 allt detta, vi kan se att vi får de 2 objekten, resten av de tomma cellerna kommer att ignoreras. Ctrl + Z. Nu, låts ange detta i cellen med Ctrl + Skift + Enter. Titta omedelbart upp till Formula Bar. Dessa lockiga parenteser är Excel som berättar att den förstod och beräknade detta som en matrisformel. Nu kan jag dubbelklicka och skicka ner den. Det ser bra ut.
Jag ska gå till den sista cellen och slå F2 för att verifiera att alla intervall ser korrekt ut. Det jag inte vill göra nu är att jag inte vill trycka på Enter eftersom den formeln efter att vi har lagt den i redigeringsläge beräknar bara korrekt om vi använder Ctrl + Skift + Enter; eller eftersom vi redan har angett formeln kan vi bara använda Esc-tangenten för att återgå till vad som finns i cellen innan vi sätter den i redigeringsläge.
Nu, låt oss testa det här. Jag kommer att klicka i den sista cellen här nere och trycka på Tab och sedan skriva ett nytt ID, Tab, Tab. En annan ny skiva, Tab, och jag kan redan se att jag inte hade tillräckligt med arbete här. Jag är, vi ska sätta - Perfekt och sedan Enter. Nu kommer detta inte automatiskt att uppdateras som om vi har en massa formler som vi räknar unika objekt och sedan extraherar unika objekt, men inga problem. Kolla på detta. Vi kan uppdatera den här listan med unika poster eftersom vi använde avancerat filter och det spelar ingen roll vilken cell du startar från, antingen när avancerat filter anropas kommer det extraheringsintervallet och de intervall som det ursprungligen tittade på lagras. Du kan klicka på Advanced Filter eller använda tangentbordet Alt + A + Q. Vi måste välja Kopiera till en annan plats, men titta på det.Det kom ihåg och utvidgades helt till A13 på grund av Excel-tabellfunktionen. Det kom ihåg extraktområdet. Jag måste bara kontrollera unika poster men klicka på OK.
Nu måste jag komma över och kopiera den här formeln. Och där går du, använder Advanced Filter och den fantastiska TEXTJOIN-funktionen med, i Array-operation för att få bara de objekt som matchar. Okej, kasta tillbaka till.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Åh hej, jag vill tacka alla för att de kom in. Vi ses nästa gång för en annan Dueling Excel-podcast från och Excel är kul.
Nedladdning fil
Ladda ner exempelfilen här: Duel183.xlsm
