Veriyi liste icerisinde bulup son karaktere kadar baska hucreye kopyalamak

Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Merhaba,

Cok dusundum ve arastirdim fakat yapmak istedigim islem icin bir formul bulamadim.

Ekledigim dosyada tum detaylari yazdim, kisaca ozetlemem gerekirse: A ve C sutunlari altinda uzun listeler var ve E sutunu altinda aranacak veri mevcut. Fakat liste icerisindeki veri aradigimizdan daha uzun. Ornek olarak 34D1 ariyoruz ve listedeki tam karsiligi 34D1-BC seklinde (yani bir sonraki bosluga kadar).

su an kullandigim formul: =IF(COUNTIF(A:A,"*"&E2&"*")>0,E2,"") (eger aradigim veri var ise E2 den ayni degeri kopyaliyor fakat tam sonucu vermiyor tabi ki)

Kullandigim Excel Ingilizce.

Ornek dosya: https://easyupload.io/mev0tc

Bahsetmeyi unuttugum onemli bir nokta kaldi ise simdiden cok ozur dilerim.

Saygilar,
Ferdi
 
Katılım
19 Ocak 2005
Mesajlar
200
Excel Vers. ve Dili
Microsoft 365 / Türkçe
E3 ve E4 den başka aranacak verinin olmadığını varsayarak, formülü örnek dosyanızda F3-H15 satırlara ekledim.
kolay gelsin
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
@hayri13 merhaba,

Yardim icin cok tesekkurler, ayrica gec cevabim icin ozur dilerim (umudu kestigim icin bir kac gundur siteye bakmayi unuttum).

1 veya en fazla 2 tane veri icin arama yaparsam formul cok guzel calisiyor. Fakat kac tane degerin bir seferde aranacagi ne yazik ki tam olarak belli degil . Bu sebeple 10 tane veri aranirsa dogal olarak formul sadece ilk ikisi icin calisacak.

Biraz daha detay vermek gerekirse, listelerin bulundugu A ve C sutunlarinda 500-600 satir olabiliyor. Ben ornek olarak sadece 13 satir koydum. Aranacak veride bazen 1-2 bazende 9-10 tane olabiliyor.

Bu formul diger bir cok islemin oldugu makronun bir parcasi olacak, yani formul ile yapmak zor ise sanirim makro olarak eklemeyi degerlendirmek lazim.

Cok tesekkurler ve saygilar,
Ferdi
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
@hayri13 elinize saglik gayet guzel calisiyor fakat simdide baska bir sorun ile karsilastim. Onceki kullandigim formulde F ve H sutunlari altinda veri her zaman yanyana oluyordu. Simdi ise veri iki listede ayri satirlarda olabildigi icin sonucta farkli satirlarda oluyor.

Ornek olarak "5DW9-BC" F13 te fakat H sutununda H12 de (gonderdiginiz ornek-SON dosyasinda gorebilirsiniz)

Bu ornek dosya gibi 6 tane daha dosya var, ayrica aranacak veriyi girdigim ve sonuclari gordugum bir tane ana sayfa var.

Yapmak istedigim tam olarak su sekilde: kullanici ana sayfada (Database) listelerde olup olmadigini kontrol edecegi veriyi B2 hucresine girer ve makroyu calistiran butona basar.

makronun iki seyi kontrol etmesi gerekiyor:

1- veri A sutunu altindaki listede varmi
2- veri C sutunu altindaki listede varmi

Sizin formulunuzle bu ikisini sayfa icerisinde gorebiliyoruz fakat ana sayfaya bunu aktarmam gerekiyor.

anasayfada girilen veri direk olarak Ornek dosyasindaki E3 ve altina kopyalaniyor

verinin A ve C sutunlarinda olup olmadigini kontrol eden formul sayenizde hazir

Anasayfaya gereken formul bu tarz bir sey olmali:

Eger E3 hucresindeki veri F ve H sutunlari altinda mevcut ise ve her ikiside 6 karakter veya uzeri ise anasayfada E2 hucresi bos kalir.

Eger E3 hucresindeki veri F veya H sutununun birinde yok ise veya 6 karakterden daha kisa ise E2 deki veri anasayfada E2 hucresinde gorunmeli.

Size gosterebilmek icin ana sayfayida ekledim ve bu arada farkettim ki formullerin bulundugu ornek sayfada E sutunu altindaki verileri sildigim halde F ve H sutunlari altinda halen sonuclar cikiyor.

Simdiden cok seymi istiyorum diye sormaya basladim kendi kendime.

Dosya: https://easyupload.io/swzrof
 
Katılım
19 Ocak 2005
Mesajlar
200
Excel Vers. ve Dili
Microsoft 365 / Türkçe
Aranacak değerlerin ilki E3 den başlayacağı durumda "DOLAYLI("E3:"&"E"&BAĞ_DEĞ_DOLU_SAY($E:$E)+1)" bu formülü oluşturmuştum. 2. datanızda E2 hücresinden başlıyor. dolayısıyla formül "DOLAYLI("E2:"&"E"&BAĞ_DEĞ_DOLU_SAY($E:$E))" olmalı.
https://easyupload.io/rminlw (e2 ye göre revizeli)
Formül aranacak en az bir değerin olacağını var sayıyor. tamamını boş bırakırsanız " " boşluğu arayacağı için tüm satırlardan veri getirir.
Ana sayfayla ilgili; aşağıdaki isteğinizi tam anlayamadım. Örnek data üzerinden anlatabilir misiniz?

Anasayfaya gereken formul bu tarz bir sey olmali:

Eger E3 hucresindeki veri F ve H sutunlari altinda mevcut ise ve her ikiside 6 karakter veya uzeri ise anasayfada E2 hucresi bos kalir.

Eger E3 hucresindeki veri F veya H sutununun birinde yok ise veya 6 karakterden daha kisa ise E2 deki veri anasayfada E2 hucresinde gorunmeli.
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Tekrar merhaba,

Anlatmakla olay giderek karistigi icin dosyayi guncelledim, su an kullanilan formulleri "Database" te E sutunu altinda ve 1KNH sayfasinda E,F,H altinda gorebilirsiniz.

"Database" B sutunu altinda 3 tane deger girdim, 2 tanesi listelerde var ve sonuc olarak E2 ve E3 bos, ucuncu deger ise listelerde olmadigi icin E4 hucresinde gorunuyor.

Amac "Database" sayfasinda sadece listede olmayanlari veya eksik olanlari gostermek. Guncel formul aranan deger varmi yokmu buluyor fakat iki eksik var.

Birincisi: "Database" E sutunu altindaki formul sadece aranan degeri "1KNH" sayfasinda F sutunu altinda kontrol ediyor. H sutununu kontrol etmiyor (daha once fark etmedigim bir yanlisim). oysa ki deger hem F hem de H altinda olmali.

Ikincisi: Sadece aradigimiz veri varmi yokmu onu kontrol ediyor. Fakat aranan deger genellikle 4 haneli fakat listedeki dogru karsiligi XXXX-X veya XXXX-XX formatinda - ornek: W7C9-BC

sonuc olarak aradigimiz XXXX her iki listedede var ise ve XXXX-X veya XXXX-XX formatinda ise (6 karakter veya uzeri) - Database bos kalacak

eger aradigimiz XXXX listenin birinde veya her ikisinde yok ise veya XXXX (dort karakter ise) - Database icinde gorunecek

Sanirim anlatmaya calistikca dahada batiriyorum, belki dosyadaki formuller daha aciklayici olacaktir.


Not: son gonderdiginiz dosyada aradigimiz deger iki listede farkli satirlarda olsada F ve H altinda yan yana gorunuyor, bu cok guzel, bos hucreleri silersem yine orijinal dosyadaki hale donecek.

Yinede ekteki dosya size fikir verebilir.

Uzgunum sizinde cok zamaninizi aldim :(
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Hayri bey tekrar merhaba,

Aradigimiz veri listenin birinde veya her ikisindede yok ise formul sorunsuz calisiyor. Tek sorun eger 1KNH sayfasinda F ve H sutunlari altinda cikan sonuc 4 haneli oldugu halde Database sayfasinda gorunmuyor. Ornek: 4S69 - her iki listedede var fakat XXXX-X veya XXXX-XX formatinda degil.

6 karakterden daha az ise sonuc yanlis oluyor ve Database sayfasinda gorunmesi gerekiyor.

Biliyorum cok zamaninizi aldim, ne kadar tesekkur etsem azdir !
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Sanirim son iki ayrinti kaldi:

1 - 1KNH sayfasinda (A) sutunu altinda 4S69-BC olursa ve (C) sutunu altinda 4S69 olursa sonuc yanlis cikiyor. Bu durumda Database te gorunmesi gerekirdi (A sutununda 4S69 olursa formul duzgun calisiyor).

2 - A veya C sutununda birisi 4S69-BC ve digeri 4S69-B olursa yine yanlis calisiyor, iki tarafta deger ayni degilse Database te gorunmeli.

Bu arada gordugum kadariyla alt sayfalardaki yardimci sutunlara gerek kalmamis. Anasayfadaki formul yeterli gibi gorunuyor, bu benim icin buyuk kolaylik olacak.

Harikasiniz :)
 

Korhan Ayhan

Administrator
Yönetici
Admin
Katılım
15 Mart 2005
Mesajlar
41,325
Excel Vers. ve Dili
Microsoft 365 Tr-En 64 Bit
Merhaba,

Ofis 365 sürümündeki yeni fonksiyonlardan faydalandım..

E2;
C++:
=IFERROR(IF(AND(LEN(VLOOKUP(B2&"*";TEXTSPLIT(TEXTJOIN(" ";1;'1KNH'!$A$2:$A$14;);;" ");1;0))>=6;
LEN(VLOOKUP(B2&"*";TEXTSPLIT(TEXTJOIN(" ";1;'1KNH'!$C$2:$C$14;);;" ");1;0))>=6);"";B2);B2)

Eski sürümler için alternatif;
E2;
C++:
=IF(B2="";"";IFERROR(IF(AND(LEN(MID(INDEX('1KNH'!A:A;MATCH("*"&B2&"*";'1KNH'!A:A;0));SEARCH(B2;INDEX('1KNH'!A:A;MATCH("*"&B2&"*";'1KNH'!A:A;0)));
SEARCH(" ";INDEX('1KNH'!A:A;MATCH("*"&B2&"*";'1KNH'!A:A;0))&" ";SEARCH(B2;INDEX('1KNH'!A:A;MATCH("*"&B2&"*";'1KNH'!A:A;0))))-
SEARCH(B2;INDEX('1KNH'!A:A;MATCH("*"&B2&"*";'1KNH'!A:A;0)))))>=6;LEN(MID(INDEX('1KNH'!C:C;MATCH("*"&B2&"*";'1KNH'!C:C;0));
SEARCH(B2;INDEX('1KNH'!C:C;MATCH("*"&B2&"*";'1KNH'!C:C;0)));SEARCH(" ";INDEX('1KNH'!C:C;MATCH("*"&B2&"*";'1KNH'!C:C;0))&"  ";
SEARCH(B2;INDEX('1KNH'!C:C;MATCH("*"&B2&"*";'1KNH'!C:C;0))))-SEARCH(B2;INDEX('1KNH'!C:C;MATCH("*"&B2&"*";'1KNH'!C:C;0)))))>=6);"";"Partial Match");B2))
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Korhan bey merhaba,

Ikinci formullerdeki noktali virgulleri virgul ile degistirerek calistirmayi basardim fakat aradigim veri satir uzerindeki son veri ise, listede yokmus gibi islem yapiyor (son veriden sonra bosluk tusuna basinca duzeliyor).

Yardiminiz icin cok tesekkurler.
 

Korhan Ayhan

Administrator
Yönetici
Admin
Katılım
15 Mart 2005
Mesajlar
41,325
Excel Vers. ve Dili
Microsoft 365 Tr-En 64 Bit
Merhaba,

Son veriden kastınızı tam olarak anlayamadım. Örnek verirseniz düzeltmeye çalışırım.
 
Katılım
12 Mart 2009
Mesajlar
119
Excel Vers. ve Dili
Office 365 - Ingilizce
Ekran goruntusu ile ornekleri gostermeye calistim, ilk satirdaki son veri 6NB1-B ve formul sonucu listede yok sonucu cikiyor. Altindaki kirmizilarda ayni sekilde. Eger aradigim veri sonda degil listenin basi ya da ortalarinda ise bir sorun olmuyor. Sanirim sonda bosluk olmamasindan kaynaklaniyor. cunku eger son karakterde bosluk tusuna basarsam sorun duzeliyor.

Foto olarak ekleyemedim, linkten gorebilirsiniz: https://www.photobox.co.uk/my/photo/full?photo_id=504910439815

Ornek dosya: https://easyupload.io/7nugjy

Kirmizi satirlar database sayfasinda listede yok seklinde gorunenler - altta yesil olan iki tane listede olanlar, sorun olmayanlar yani.
 

Korhan Ayhan

Administrator
Yönetici
Admin
Katılım
15 Mart 2005
Mesajlar
41,325
Excel Vers. ve Dili
Microsoft 365 Tr-En 64 Bit
Önerdiğim formülü revize ettim. Son halini tekrar deneyiniz.
 
Üst