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?

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 Araçlar > 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:

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:

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 Üretkenliği Araçları

Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır

Yeniden: Hızlıca yerleştirin karmaşık formüller, grafikler ve daha önce kullandığınız her şey; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
Hücreleri / Satırları / Sütunları Birleştirme Veri kaybetmeden; Bölünmüş Hücre İçeriği; Yinelenen Satırları / Sütunları Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
300'den fazla güçlü özellik. Office / Excel 2007-2019 ve 365'i destekler. Tüm dilleri destekler. Kuruluşunuzda veya kuruluşunuzda kolay dağıtım. Tam özellikli 30 günlük ücretsiz deneme. 60 günlük para iade garantisi.

Devamı ... Ücretsiz indirin... Satın alma...

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!

Devamı ... Ücretsiz indirin... Satın alma...

13 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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,
- Reply
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
- Reply
- 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?
  
  Reply
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
- Reply
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
- Reply
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?
- Reply
- 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.
  
  Reply
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?
- Reply
- 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.
  
  Reply
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?
- Reply
- 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.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  clifford · 3 months ago
  
  Hi skyyang what if you want the result as a column?
  
  Reply
  
  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
  
  Reply

Hesabınıza Giriş

Parola Sıfırlama