Veri doğrulama hücresi içerisinde arama yapmak

[kkaplan]

Merhaba,

Excel 2016 üzerinden veri doğrulama ile oluşturduğum bir hücrem var bunun içerisinde çok fazla tekrarlı tekrarsız çok fazla ve uzun olduğundan veri mevcut. Bunu hücreye örnek domates.. yazdığımda veri doğrulama ile hücre içerisinde aşağıya açılır şekilde içerisinde domates geçen ürünleri bulup listelemesini istiyorum. Ancak forumda bu şekilde bir konuya denk gelmedim office 365 üzerinden bu şekilde bir şey mevcut ancak bende de 365 olmadığında filtre fonsiyonu ile de b işlemi tamamlayamıyorum. Makro veya combox listbox gibi form denetimleri ile de yapılabiliyorsa desteklerinizi rica ediyorum.

Satış sayfamda a2 hücresinde veri doğrulama mevcut buradan sayfa1 de yer alan a sütunundaki verileri seçerek doğrulama yapıyor.

Örnek uygulama dosyam.

soldan2.xlsm dosyasını indir - download

soldan2.xlsm dosyasını indir, download. Dosya.tc .Dosya Upload. Dosya Paylaş. Dosya Yükle

s6.dosya.tc

 

[ÖmerFaruk]

Satış sayfasının KOD sayfasına aşağıdakini yapıştırın.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [A1]) Is Nothing Then Exit Sub
    Dim Liste As String, Veri As Variant, i As Integer, ListItem As Variant
    For Each ListItem In Worksheets("Sayfa1").Range("A2").Resize(Worksheets("Sayfa1").Range("A1").End(xlDown).Row - 1, 1)
        If InStr(1, LCase(ListItem), LCase(Range("A1"))) > 0 Then
            If Len(Liste) = 0 Then
                Liste = Replace(ListItem, ",", Chr(130))
            Else
                Liste = Liste & "," & Replace(ListItem, ",", Chr(130))
            End If
        End If
    Next ListItem
    If Liste = "" Then MsgBox Range("A1") & " ifadesi içeren bir satır bulunamadı": Exit Sub
    With ActiveSheet.Range("A2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Liste
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
End Sub

A1 hücresine "dom" yazıp entera basınca A2 hücresinde Veri Doğrulama listesinde Sayfa1 deki tabloda A sütununda içerisinde "dom" geçen satırlar tekrasız olarak listeleneceltir.
Eğer sayfa1deki A sütununda tamamen aynı satırlar varsa bu kod tekrarlananı listelemez. Ben örnek dosyanızdaki verilerin kop-paste yapıldığı için tekrarlandığını düşündüm. Aksi durumda kodları değiştirmek lazım gelecektir.

 

[kkaplan]

Satış sayfasının KOD sayfasına aşağıdakini yapıştırın.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [A1]) Is Nothing Then Exit Sub
    Dim Liste As String, Veri As Variant, i As Integer, ListItem As Variant
    For Each ListItem In Worksheets("Sayfa1").Range("A1").CurrentRegion.Value
        Aranan = LCase(ListItem)
        If InStr(1, LCase(ListItem), LCase(Range("A1"))) > 0 Then
            If Len(Liste) = 0 Then
                Liste = ListItem
            Else
                Liste = Liste & "," & ListItem
            End If
        End If
    Next ListItem
    With ActiveSheet.Range("A2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Liste
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
End Sub

A1 hücresine "dom" yazıp entera basınca A2 hücresinde Veri Doğrulama listesinde Sayfa1 deki tabloda A sütununda içerisinde "dom" geçen satırlar tekrasız olarak listeleneceltir.
Eğer sayfa1deki A sütununda tamamen aynı satırlar varsa bu kod tekrarlananı listelemez. Ben örnek dosyanızdaki verilerin kop-paste yapıldığı için tekrarlandığını düşündüm. Aksi durumda kodları değiştirmek lazım gelecektir.

Ömer bey destekleriniz için teşekkür ederim kod çalışıyor ancak arama yaptıktan sonra veri doğrulama hücresini tekrar açtığımda sayfa1 de yer alan tüm verileri açılır kutuda gösteriyor. Onun dışında istediğim şekilde kodlar çalışıyor. Veri doğrulamanın sadece a sütunu için geçerli olması konusunda yardımcı olabilir misiniz ?

 

[ÖmerFaruk]

Kodu revize ettim. Önceki mesajdan tekrar kopyalayın.

 

[kkaplan]

Kodu revize ettim. Önceki mesajdan tekrar kopyalayın.

Ömer bey destekleriniz için çok çok teşekkür ederim, biliyorum çok oldum ama son olarak veri doğrulamada "21.10.2023--Biber Salçası--61,5384615384615" bu şekilde veri varsa buradaki "," virgülden sonraki değer bir alt satıra atıyor birlikte seçime izin vermiyor. izin vermediği içinde de hatalı kayıt edilmiş oluyor. Sizden son rica bunu da düzeltebilirseniz çok çok memnun olurum emekleriniz içinde tekrar teşekkürler.

 

[ÖmerFaruk]

Kodu revize ettim. Yukarıdan tekrar kopyalayın.
Ayrıca B6 daki formülünüzde de küçük bir ilave yapmak zorunda kaldım.

=EĞERHATA(DÜŞEYARA(YERİNEKOY(A6;DAMGA(130);",");Sayfa1!A:B;2;0);"")

Yaptığım işlemin açıklaması şu
VeriDoğrulama listesinde liste öğelerini virgül ile ayırmak zorunda olduğumuz için, sizin hücre aralığınızdaki virgülleri benzer karakter olan ASC 130 ile değiştirip doğrulama listesine öyle ekliyorum.
Böylece Veri Doğrulama düzgün çalışıyor.
Formülle tutarları bulurken tekrar işlemi tersine çevirip ASC130 yerine "," koyarak Düşeyara fonksiyonunu kullanıyorum.

 

[kkaplan]

Kodu revize ettim. Yukarıdan tekrar kopyalayın.
Ayrıca B6 daki formülünüzde de küçük bir ilave yapmak zorunda kaldım.

=EĞERHATA(DÜŞEYARA(YERİNEKOY(A6;DAMGA(130);",");Sayfa1!A:B;2;0);"")

Yaptığım işlemin açıklaması şu
VeriDoğrulama listesinde liste öğelerini virgül ile ayırmak zorunda olduğumuz için, sizin hücre aralığınızdaki virgülleri benzer karakter olan ASC 130 ile değiştirip doğrulama listesine öyle ekliyorum.
Böylece Veri Doğrulama düzgün çalışıyor.
Formülle tutarları bulurken tekrar işlemi tersine çevirip ASC130 yerine "," koyarak Düşeyara fonksiyonunu kullanıyorum.

Hocam çok teşekkür ederim anca deneme fırsatım oldu. Çok teşekkür ederim destekleriniz için.

 

[ÖmerFaruk]

Rica ederim. Kolay gelsin.