Ana içeriğe atla

Excel'de kopyalar olmadan birden çok değeri nasıl vlookup ve döndürürüm? 

Bazen, birden çok eşleşen değeri aynı anda tek bir hücreye bakmak ve döndürmek isteyebilirsiniz. Ancak, döndürülen hücrelere doldurulmuş bazı tekrarlanan değerler varsa, Excel'de gösterilen aşağıdaki ekran görüntüsü gibi tüm eşleşen değerleri döndürürken nasıl kopyaları yoksayabilir ve yalnızca benzersiz değerleri koruyabilirsiniz?

doc birden çok benzersiz değer döndür 1

Vlookup ve Kullanıcı Tanımlı İşlevini kullanarak yinelemesiz birden çok eşleşen değer döndür

Vlookup ve Kullanıcı Tanımlı İşlevini kullanarak yinelemesiz birden çok eşleşen değer döndür

Aşağıdaki VBA kodu, kopyalar olmadan birden çok eşleşen değeri döndürmenize yardımcı olabilir, lütfen şu şekilde yapın:

1. Basılı tutun Alt + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.

2. tıklayın Ekle > modülve aşağıdaki kodu modül Pencere.

VBA kodu: Vlookup ve birden çok benzersiz eşleşen değer döndür:

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. Kodu ekledikten sonra, ardından Tools > Referanslar açıldığında Uygulamalar için Microsoft Visual Basic pencerede ve ardından dışarıda Referanslar - VBAProject iletişim kutusu, işaretleyin Microsoft Komut Dosyası Çalışma Zamanı seçeneği Mevcut Referanslar liste kutusu, ekran görüntüsüne bakın:

doc birden çok benzersiz değer döndür 2

4. Sonra tıklayın OK iletişim kutusunu kapatmak, kod penceresini kaydedip kapatmak, çalışma sayfasına geri dönün ve şu formülü girin: =MultipleLookupNoRept(E2,A2:C17,3) sonucun çıktısını almak istediğiniz boş bir hücreye Keşfet İhtiyacınız olan doğru sonucu almak için anahtar. Ekran görüntüsüne bakın:

doc birden çok benzersiz değer döndür 3

not: Yukarıdaki formülde, E2 bakmak istediğiniz kriterler, A2: C17 kullanmak istediğiniz veri aralığı, numara 3 döndürülen değerleri içeren sütun numarasıdır.

En İyi Ofis Üretkenlik Araçları

🤖 Kutools AI Yardımcısı: Aşağıdakilere dayalı olarak veri analizinde devrim yaratın: Akıllı Yürütme   |  Kodunu oluşturun  |  Özel Formüller Oluşturun  |  Verileri Analiz Edin ve Grafikler Oluşturun  |  Kutools İşlevlerini Çağır...
Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla   |  Boş Satırları Sil   |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin   |   Formülsüz Tur ...
Süper Arama: Çoklu Ölçütlü VLookup  |   Çoklu Değer VLookup  |   Birden Çok Sayfada VLookup   |   Bulanık Arama ....
Gelişmiş Açılır Liste: Hızla Açılır Liste Oluşturun   |  Bağımlı Açılır Liste   |  Çoklu Seçim Açılır Liste ....
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  |  Aralıkları ve Sütunları Karşılaştırın ...
Öne Çıkan Özellikler: Izgara Odağı   |  Tasarım görünümü   |   Büyük Formül Çubuğu   |  Çalışma Kitabı ve Sayfa Yöneticisi   |  Kaynak Kütüphanesi (Otomatik metin)   |  Tarih Seçici   |  Çalışma Sayfalarını Birleştirin   |  Hücreleri Şifrele/Şifresini Çöz   |  E-postaları Listeye Göre Gönder   |  Süper Filtre   |   Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti:  12 Metin Tools (Metin ekle, Karakterleri Kaldır, ...)   |   50+ Grafik Türleri (Gantt şeması, ...)   |   40+ Pratik Formüller (Yaşı doğum gününe göre hesapla, ...)   |   19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle, ...)   |   12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme, ...)   |   7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Bölünmüş hücreler, ...)   |   ... ve dahası

Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar.  En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...

Açıklama

Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir

  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
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