Note: The other languages of the website are Google-translated. Back to English
Logga in  \/ 
x
or
x
Registrera  \/ 
x

or

Hur slår jag upp och returnerar flera värden utan dubbletter i Excel? 

Ibland kanske du vill söka efter och returnera flera matchade värden i en enda cell samtidigt. Men om det finns några upprepade värden som fylls i de returnerade cellerna, hur kan du ignorera dubbletterna och bara behålla de unika värdena när du returnerar alla matchande värden som följande skärmdump visas i Excel?

doc returnerar flera unika värden 1

Vlookup och returnera flera matchande värden utan dubbletter med användardefinierad funktion


Vlookup och returnera flera matchande värden utan dubbletter med användardefinierad funktion

Följande VBA-kod kan hjälpa dig att returnera flera matchande värden utan dubbletter, gör så här:

1. Håll ner Alt + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulerna Fönster.

VBA-kod: Vlookup och returnera flera unika matchade värden:

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. När du har infogat koden klickar du sedan på verktyg > referenser i det öppnade Microsoft Visual Basic för applikationer fönstret och sedan, i poppade ut Referenser - VBAProject dialogruta, kontrollera Microsoft Scripting Runtime alternativet i Tillgängliga referenser listruta, se skärmdump:

doc returnerar flera unika värden 2

4. Klicka sedan OK för att stänga dialogrutan, spara och stäng kodfönstret, gå tillbaka till kalkylbladet och skriv in denna formel: =MultipleLookupNoRept(E2,A2:C17,3) till en tom cell där du vill mata ut resultatet, tryck på ange för att få rätt resultat efter behov. Se skärmdump:

doc returnerar flera unika värden 3

Anmärkningar: I ovanstående formel, E2 är kriterierna som du vill titta på, A2: C17 är det dataområde du vill använda, numret 3 är kolumnnumret som innehåller de returnerade värdena.


De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50%och minskar hundratals musklick för dig varje dag!
officetab botten
Say something here...
symbols left.
You are guest
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.
    Drew · 1 years ago
    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,
  • To post as a guest, your comment is unpublished.
    Jame · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Jame,
      Could you give your problem as a screenshot here, so that i can understand your requires?
  • To post as a guest, your comment is unpublished.
    srikanth · 2 years 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.
    rasike001 · 2 years 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 · 2 years 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 · 2 years 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 · 3 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 · 3 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 · 3 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 · 3 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.
        clifford · 3 months ago
        Hi skyyang what if you want the result as a column?
      • To post as a guest, your comment is unpublished.
        rasike001 · 2 years 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