Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe vloekup en retourneer meerdere waarden zonder duplicaten in Excel?

Soms wilt u mogelijk meerdere overeenkomende waarden tegelijkertijd in één cel storten en retourneren. Maar als er een aantal herhaalde waarden zijn ingevuld in de geretourneerde cellen, hoe zou u dan de duplicaten kunnen negeren en alleen de unieke waarden behouden bij het retourneren van alle overeenkomende waarden als het volgende screenshot in Excel?

doc retourneert meerdere unieke waarden 1

Vlookup en retourneer meerdere overeenkomende waarden zonder duplicaten met behulp van Door de gebruiker gedefinieerde functie


Vlookup en retourneer meerdere overeenkomende waarden zonder duplicaten met behulp van Door de gebruiker gedefinieerde functie

De volgende VBA-code kan u helpen meerdere overeenkomende waarden zonder duplicaten te retourneren, doe dit als volgt:

1. Houd de toets ingedrukt Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: Vlookup en retourneer meerdere unieke overeenkomende waarden:

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. Nadat u de code hebt ingevoegd, klikt u op Gereedschap > Referenties in de geopende Microsoft Visual Basic voor toepassingen venster, en vervolgens in de popping out Referenties - VBAProject dialoogvenster Microsoft Scripting Runtime optie in het Beschikbare verwijzingen keuzelijst, zie screenshot:

doc retourneert meerdere unieke waarden 2

4. Dan klikken OK om het dialoogvenster te sluiten, sla het codevenster op en sluit het, ga terug naar het werkblad en voer deze formule in: =MultipleLookupNoRept(E2,A2:C17,3) in een lege cel waar u het resultaat wilt uitvoeren, druk op invoeren sleutel om het juiste resultaat te krijgen als u dat nodig hebt. Zie screenshot:

doc retourneert meerdere unieke waarden 3

Notes: In de bovenstaande formule, E2 is het criterium dat je wilt vlookup, A2: C17 is het gegevensbereik dat u wilt gebruiken, het nummer 3 is het kolomnummer dat de geretourneerde waarden bevat.


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Werkt met Office 2007-2019 en 365 en ondersteunt alle talen. Het is eenvoudig te implementeren in uw bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    srikanth · 1 months ago
    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
  • To post as a guest, your comment is unpublished.
    Rasike · 5 months ago
    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
  • To post as a guest, your comment is unpublished.
    Imre · 6 months ago
    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?
    • To post as a guest, your comment is unpublished.
      skyyang · 6 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Demetre · 1 years ago
    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?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    Tom · 1 years ago
    what if I wanted to create a list in a table from this instead of all results in one cell?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Rasike · 6 months ago
        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