Preskoči na glavno vsebino

Kako v Excel iskati in vrniti več vrednosti brez dvojnikov? 

Avtor: Xiaoyang Zadnja sprememba: 2018-05-03

Včasih boste morda želeli vlookup in vrniti več ujemajočih se vrednosti v eno celico hkrati. Toda, če je v vrnjene celice vnesenih nekaj ponavljajočih se vrednosti, kako lahko prezrete dvojnike in ohranite edinstvene vrednosti le, če vrnete vse ujemajoče se vrednosti, kot je prikazano na sliki zaslona, ​​prikazani v Excelu?

doc vrne več enoličnih vrednosti 1

Vlookup in vrnitev več ujemajočih se vrednosti brez dvojnikov z uporabniško določeno funkcijo


Vlookup in vrnitev več ujemajočih se vrednosti brez dvojnikov z uporabniško določeno funkcijo

Naslednja koda VBA vam lahko pomaga vrniti več ujemajočih se vrednosti brez dvojnikov. Naredite to:

1. Držite tipko Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Vlookup in vrnitev več unikatnih ujemajočih se vrednosti:

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 vstavitvi kode kliknite Orodja > Reference v odprtem Microsoft Visual Basic za aplikacije okno in nato v izpuščenem Reference - VBAProject pogovorno okno, preverite Izvajanje Microsoftovih skriptov možnost v Razpoložljive reference seznam, glej posnetek zaslona:

doc vrne več enoličnih vrednosti 2

4. Nato kliknite OK da zaprete pogovorno okno, shranite in zaprite okno s kodo, se vrnite na delovni list in vnesite to formulo: =MultipleLookupNoRept(E2,A2:C17,3) v prazno celico, kjer želite izpisati rezultat, pritisnite Vnesite tipko, da dobite pravi rezultat, kot ga potrebujete. Oglejte si posnetek zaslona:

doc vrne več enoličnih vrednosti 3

Opombe: V zgornji formuli, E2 je merilo, ki ga želite pregledati, A2: C17 je obseg podatkov, ki ga želite uporabiti, število 3 je številka stolpca, ki vsebuje vrnjene vrednosti.

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi , if there is no result found then how to make it show "NO RESULT" instead of just normal blank
This comment was minimized by the moderator on the site
Hello, Pho,
When there are no matching entries found in the specified column for the given lookup value, make it show "NO RESULT" instead of just normal blank. To finish this task, please apply the below code:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) As String
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Cells(i, 1).Value = Lookupvalue Then
            If Not xDic.Exists(LookupRange.Cells(i, ColumnNumber).Value) Then
                xDic.Add LookupRange.Cells(i, ColumnNumber).Value, Nothing
            End If
        End If
    Next i
    xStr = ""
    If xDic.Count > 0 Then
        For Each Key In xDic.Keys
            xStr = xStr & Key & ","
        Next Key
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    Else
        MultipleLookupNoRept = "NO RESULT"
    End If
End Function


Please have a try, hope it can help you!
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,
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
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?
This comment was minimized by the moderator on the site
Hi , what should I do if want the result to show up as "NV" instead of blank
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
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
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?
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.
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?
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.
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?
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.
This comment was minimized by the moderator on the site
Hi skyyang what if you want the result as a column?
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
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations