Ugrás a tartalomra

Hogyan lehet vlookupot készíteni és több értéket visszaadni duplikátumok nélkül az Excelben? 

Előfordulhat, hogy érdemes vlookup-ot keresnie, és egyszerre több egyező értéket visszaadni egyetlen cellába. De ha néhány ismételt érték van feltöltve a visszaküldött cellákban, hogyan hagyhatja figyelmen kívül a duplikátumokat, és csak akkor őrizheti meg az egyedi értékeket, amikor az összes egyező értéket visszaküldi az Excelben bemutatott képernyőképet követve?

A doc több egyedi értéket ad vissza 1

Vlookup és több egyező értéket adhat meg ismétlés nélkül a Felhasználó által definiált funkció használatával


Vlookup és több egyező értéket adhat meg ismétlés nélkül a Felhasználó által definiált funkció használatával

A következő VBA-kód segíthet abban, hogy több egyező értéket adjon meg ismétlés nélkül, kérjük, tegye a következőket:

1. Tartsa lenyomva a Alt + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.

2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modulok Ablak.

VBA kód: Vlookup és több egyedi egyeztetett értéket ad vissza:

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. A kód beillesztése után kattintson a gombra Eszközök > Referenciák a nyitottban Microsoft Visual Basic for Applications ablakot, majd beugrott Referenciák - VBAProject párbeszédpanelen ellenőrizze Microsoft Scripting Runtime lehetőség a Elérhető referenciák lista mező, lásd a képernyőképet:

A doc több egyedi értéket ad vissza 2

4. Ezután kattintson OK a párbeszédpanel bezárásához mentse el és zárja be a kódablakot, térjen vissza a munkalapra, és írja be ezt a képletet: =MultipleLookupNoRept(E2,A2:C17,3) egy üres cellába, ahol az eredményt ki szeretné adni, nyomja meg a gombot belép gombot a kívánt eredmény eléréséhez. Lásd a képernyőképet:

A doc több egyedi értéket ad vissza 3

Megjegyzések: A fenti képletben E2 az a kritérium, amelyet vlookupba szeretne venni, A2: C17 a használni kívánt adattartomány, a szám 3 az oszlop száma, amely tartalmazza a visszaadott értékeket.

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
Comments (13)
No ratings yet. Be the first to rate!
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,
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