Přejít k hlavnímu obsahu

Jak vlookup a vrátit více hodnot bez duplikátů v aplikaci Excel? 

Někdy můžete chtít vlookup a vrátit více shodných hodnot do jedné buňky najednou. Ale pokud se do vrácených buněk naplní několik opakovaných hodnot, jak byste mohli ignorovat duplikáty a zachovat pouze jedinečné hodnoty při vracení všech odpovídajících hodnot jako následující snímek obrazovky zobrazený v aplikaci Excel?

doc vrátí více jedinečných hodnot 1

Vlookup a vrátit více hodnot shody bez duplikátů pomocí funkce definované uživatelem

Vlookup a vrátit více hodnot shody bez duplikátů pomocí funkce definované uživatelem

Následující kód VBA vám pomůže vrátit více shodných hodnot bez duplikátů, udělejte to takto:

1. Podržte Alt + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: Vlookup a vrátit více jedinečných shodných hodnot:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Po vložení kódu klikněte na Tools > Reference v otevřeném Microsoft Visual Basic pro aplikace okno a poté ve vyskakovacím okně Reference - VBAProject v dialogovém okně zaškrtněte Microsoft Scripting Runtime možnost v Dostupné reference seznam, viz screenshot:

doc vrátí více jedinečných hodnot 2

4. Pak klikněte na tlačítko OK zavřete dialogové okno, uložte a zavřete okno s kódem, vraťte se do listu a zadejte tento vzorec: =MultipleLookupNoRept(E2,A2:C17,3) do prázdné buňky, kam chcete výsledek odeslat, stiskněte vstoupit klíč k získání správného výsledku, jak potřebujete. Viz screenshot:

doc vrátí více jedinečných hodnot 3

Poznámka: Ve výše uvedeném vzorci, E2 jsou kritéria, která chcete zobrazit, A2: C17 je rozsah dat, který chcete použít, číslo 3 je číslo sloupce, které obsahuje vrácené hodnoty.

Nejlepší nástroje pro produktivitu v kanceláři

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup  |   VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar   |  Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky   |  Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů:  12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

Popis

Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Comments (13)
No ratings yet. Be the first to rate!
Drew
Drew
This comment was minimized by the moderator on the site
This is great! How would I adapt this to not add null values to the dictionary? I've tried adding the bold below, but the final string is still returning with ,"", instances.


xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue And Not IsEmpty(LookupRange.Columns(1).Cells(i).Value) Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next

Thanks,
Reply
0
0
Jame
Jame
This comment was minimized by the moderator on the site
Hello , I did as u told and it great but it still havent solve one of my problem , what happen when u unique value in each month ? =MultipleLookupNoRept(E2,A2:C17,3) , i try to E2&1 for January but it not working
Reply
0
0
skyyang
skyyang     Jame
This comment was minimized by the moderator on the site
Hi, Jame,
Could you give your problem as a screenshot here, so that i can understand your requires?
Reply
Report
0
0
srikanth
srikanth
This comment was minimized by the moderator on the site
hi,
while the time of lot value multivlooks my worksheet got hang.is there any other ways to multivlookupwithoutrepeation????

and also i used on new desktop also its getting hang only...

my data value is around 10,000 rows
Reply
0
0
rasike001
rasike001
This comment was minimized by the moderator on the site
Hi

I wanted to create a list in a table from this instead of all results in one cell. So I have used a formula similar below (what you have suggested)

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

However, this is taking a long time to process from a large set of data.
Is there any alternative method to process this faster?
Thanks again
Rasike
Reply
Report
0
0
Imre
Imre
This comment was minimized by the moderator on the site
xStr = xStr & xDic.Keys(I) & "," to be this: xStr = xStr & xDic.Keys(I) & ", "

Is there a way to replace "," with in-cell ALT+ENTER, so that the results will be in the same cell but on different lines? Do I need to introduce additional VBA module for that and combine them?

Also, this code is quite slow when looping over huge tables. Anyone knows any faster solutions?
Reply
0
0
skyyang
skyyang     Imre
This comment was minimized by the moderator on the site
Hi, Imre,
To separate the result values by Alt + Enter keys, please apply the following User Defined Function:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim xDic As New Dictionary
Dim xRows As Long
Dim xStr As String
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
If xDic.Count > 0 Then
For i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & Chr(10) + Chr(13)
Next
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
Debug.Print xStr
End Function

And then do with the above steps in this article, at last, after entering the formula, you should click Wrap Text under the Home tab.
View Attachments (1 / 5)
Reply
Report
0
0
Demetre
Demetre
This comment was minimized by the moderator on the site
Is there a way to add a space in between the multiple values retrieved in the results without introducing a comma at the end of the list? For example your result above would show as: "Emily, James, Daisy, Gary" instead of like this: "Emily,James,Daisy,Gary"

I tried to edit this portion of the VBA code: xStr = xStr & xDic.Keys(I) & "," to be this: xStr = xStr & xDic.Keys(I) & ", "

That did add the space in between the values, but it also added a comma after the last value. "Emily, James, Daisy, Gary,"

Is there a way to make it work with the space but without the extra comma after the last value?
Reply
0
0
skyyang
skyyang     Demetre
This comment was minimized by the moderator on the site
Hello, Demetre,
Use the space to separate the values, you just need to change the vba code:
from xStr = xStr & xDic.Keys(i) & "," to be this: xStr = xStr & xDic.Keys(i) & " "

Please try it.
Reply
Report
0
0
Tom
Tom
This comment was minimized by the moderator on the site
what if I wanted to create a list in a table from this instead of all results in one cell?
Reply
0
0
skyyang
skyyang     Tom
This comment was minimized by the moderator on the site
Hello, Tom,
If you want to extract the unique values in a list of cells instead of one cell, the following formula may help you:

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Please try it.
View Attachments (1 / 5)
Reply
Report
0
0
clifford
clifford     skyyang
This comment was minimized by the moderator on the site
Hi skyyang what if you want the result as a column?
Reply
0
0
rasike001
rasike001     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,

Thank you very much for this formula.
This works for me. However, it is taking a long time to process from a large set of data.
Can we modify this formula to work this bit faster?
Thanks again
Rasike
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations