Çözüldü Farklı Sayfalardaki Verileri Tek Sütunda Toplama
- Konuyu başlatan brkayahan
- Başlangıç tarihi
Çözüm
Merhaba @brkayahan .
Sorularınızı, ekran görüntüsü yerine, cevabımın altındaki İMZA bölümünde göreceğiniz açıklamalar doğrultusunda, gerçek belgenizin özel bilgi içermeyen kopyası şeklinde hazırlayacağınız örnek belge üzerinden sorarsanız daha hızlı sonuç almanız kolaylaşır.
.
Sorularınızı, ekran görüntüsü yerine, cevabımın altındaki İMZA bölümünde göreceğiniz açıklamalar doğrultusunda, gerçek belgenizin özel bilgi içermeyen kopyası şeklinde hazırlayacağınız örnek belge üzerinden sorarsanız daha hızlı sonuç almanız kolaylaşır.
.
@Ömer BARAN söylediğiniz şekilde dosyayı paylaştım.
Ürün barkodlarını ve bedenlerini yanına çekerek birinci tabloyu tamamlamaya yardımcı olursanız sevinirim.
Ürün barkodlarını ve bedenlerini yanına çekerek birinci tabloyu tamamlamaya yardımcı olursanız sevinirim.
İki-üç sayfadaki verileri olması gerektiği gibi elle kopyala yapıştır yaparak örneklendirip,
aktarılacak verilerin olduğu sayfa adeti, sayfa isimleri ve sayfalardaki dolu satır adetlerinin
sabit mi değişken mi olduğu konusunu da netleştirecek şekilde yeni bir örnek belge yükleyin ki ikircikli/tereddütlü bir konu kalmasın.
Tahminim o ki; isteğiniz için makro kullanılması daha pratik olacak gibi görünüyor.
aktarılacak verilerin olduğu sayfa adeti, sayfa isimleri ve sayfalardaki dolu satır adetlerinin
sabit mi değişken mi olduğu konusunu da netleştirecek şekilde yeni bir örnek belge yükleyin ki ikircikli/tereddütlü bir konu kalmasın.
Tahminim o ki; isteğiniz için makro kullanılması daha pratik olacak gibi görünüyor.
Selamlar,
Profilinizde Excel 365 görünmekte..
365 dinamik dizi formülleriyle çözüm isterseniz,
Sheet1 A2 hücresine
Türkçe olarak,
=LET(a;XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!A2:C1000)&"</b></a>";"//b");b;SATIRSAY(a);c;SIRALI(b);ELEMAN({1;2};FİLTRE(a;MOD(c;3)=1);FİLTRE(a;MOD(c;3)=0)))
yazınız... Dökülmeli olarak sonuçlar gelecektir.
(Not : Sheet1 A kolonunun Sayı biçimlendirmesini de diğer sayfalarda olduğu gibi Sayı , Ondalık 0 şeklinde ayarlayın)
İngilizce kullandığınızdan
=LET(a;FILTERXML("<a><b>"&JOINTEXT("</b><b>";;'B1:B10'!A2:C1000)&"</b></a>";"//b");b;ROWS(a);c;SEQUENCE(b);CHOOSE({1;2};FILTER(a;MOD(c;3)=1);FILTER(a;MOD(c;3)=0)))
Excel Versiyonunuzda Belki, formüldeki ayıraçlar ; (Noktalı Virgül) yerine , (Virgül) olabilir... O halde
=LET(a,FILTERXML("<a><b>"&JOINTEXT("</b><b>",,'B1:B10'!A2:C1000)&"</b></a>","//b"),b,ROWS(a),c,SEQUENCE(b),CHOOSE({1,2},FILTER(a,MOD(c,3)=1),FILTER(a,MOD(c,3)=0)))
Burada
'B1:B10'!A2:C1000
Sheet1 den sonraki ilk sayfa B1 ve son sayfa B10 (resimde ise BE17 ise 'B1:BE17'!A2:C1000 şeklinde olacak)
A2 : C1000 yerine sayfalardaki en geniş aralığa göre alan belirleyiniz... (Örneğin A2 : C150 gibi)
Koşul : TÜM sayfalarda, A kolonlarında A2 den itibaren Beden kodları ve C2 den itibaren C kolonunda ise Bedenler bulunmalıdır...
Dosyanız ektedir...
Profilinizde Excel 365 görünmekte..
365 dinamik dizi formülleriyle çözüm isterseniz,
Sheet1 A2 hücresine
Türkçe olarak,
=LET(a;XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!A2:C1000)&"</b></a>";"//b");b;SATIRSAY(a);c;SIRALI(b);ELEMAN({1;2};FİLTRE(a;MOD(c;3)=1);FİLTRE(a;MOD(c;3)=0)))
yazınız... Dökülmeli olarak sonuçlar gelecektir.
(Not : Sheet1 A kolonunun Sayı biçimlendirmesini de diğer sayfalarda olduğu gibi Sayı , Ondalık 0 şeklinde ayarlayın)
İngilizce kullandığınızdan
=LET(a;FILTERXML("<a><b>"&JOINTEXT("</b><b>";;'B1:B10'!A2:C1000)&"</b></a>";"//b");b;ROWS(a);c;SEQUENCE(b);CHOOSE({1;2};FILTER(a;MOD(c;3)=1);FILTER(a;MOD(c;3)=0)))
Excel Versiyonunuzda Belki, formüldeki ayıraçlar ; (Noktalı Virgül) yerine , (Virgül) olabilir... O halde
=LET(a,FILTERXML("<a><b>"&JOINTEXT("</b><b>",,'B1:B10'!A2:C1000)&"</b></a>","//b"),b,ROWS(a),c,SEQUENCE(b),CHOOSE({1,2},FILTER(a,MOD(c,3)=1),FILTER(a,MOD(c,3)=0)))
Burada
'B1:B10'!A2:C1000
Sheet1 den sonraki ilk sayfa B1 ve son sayfa B10 (resimde ise BE17 ise 'B1:BE17'!A2:C1000 şeklinde olacak)
A2 : C1000 yerine sayfalardaki en geniş aralığa göre alan belirleyiniz... (Örneğin A2 : C150 gibi)
Koşul : TÜM sayfalarda, A kolonlarında A2 den itibaren Beden kodları ve C2 den itibaren C kolonunda ise Bedenler bulunmalıdır...
Dosyanız ektedir...
@Ömer BARAN Dosyayı revize ederek tekrar yükledim.
Beden barkodu ve rengi sayfalarda sabit.
B1 den B7ye kadar olan bütün sayfaların A2,A3,A4 hücrelerindeki Beden Barkodlarını ve C2, C3, C4 hücrelerindeki bedenlerini Sheet1 sayfasına eklemeyi istiyorum.
Ürün kodunu çekmek için farklı bedenleri çekmek için farklı formül kullanılacağını düşünüyorum ama siz ne dersiniz?
Beden barkodu ve rengi sayfalarda sabit.
B1 den B7ye kadar olan bütün sayfaların A2,A3,A4 hücrelerindeki Beden Barkodlarını ve C2, C3, C4 hücrelerindeki bedenlerini Sheet1 sayfasına eklemeyi istiyorum.
Ürün kodunu çekmek için farklı bedenleri çekmek için farklı formül kullanılacağını düşünüyorum ama siz ne dersiniz?
Eğer, 5 no lu mesajımda önerdiğim çözümü
Excel2019 ve üzeri versiyonda kullanacaksanız.. (365 de Dökülmeli olmayacaktır.)
A2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+1);"")
B2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+3);"")
yazıp aşağıya doğru kopyalayın...
Burada
EĞERHATA : IFERROR
İNDİS : INDEX
XMLFİLTRELE : FILTERXML (Excel2013 ve yukarısı)
METİNBİRLEŞTİR : JOINTEXT (Excel2019 ve yukarısı)
SATIRSAY : ROWS
Excel2019 ve üzeri versiyonda kullanacaksanız.. (365 de Dökülmeli olmayacaktır.)
A2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+1);"")
B2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+3);"")
yazıp aşağıya doğru kopyalayın...
Burada
EĞERHATA : IFERROR
İNDİS : INDEX
XMLFİLTRELE : FILTERXML (Excel2013 ve yukarısı)
METİNBİRLEŞTİR : JOINTEXT (Excel2019 ve yukarısı)
SATIRSAY : ROWS
Çok teşekkür ederim bu formül ile istediğim işlemi yapabiliyorum
Egerki zahmet olmazsa ezberlemek degilde öğrenmek adına, adım adım hangi mantıkla formülü yazdığınızı anlatabilir misiniz ?
Emeginize saglık
Selamlar,
Beden Kodlarını listelemek için kullandığımız
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+1);"")
formülünde,
XMLFİLTRELE işlevi ana işlev olup, XMLFİLTRELE işlevi belirtilen xpath'i kullanarak XML içeriğinden belirli verileri döndürür. Buradaki yapı standarttır.
Yani
Bunun için ilk önce, yazılan metinleri geçerli bir XML e dönüştürmek gerekir.
Örneğin A1 hücresinde sadece Boşluklar kullanarak yazılmış bir cümle olsun.
"<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>")
ile A1 hücresinde kelimeler arasındaki boşlukları "</a><a>" yaparak cümle içindeki kelimeleri içeren XML yapısı elde edilir.
XMLFİLTRELE ile
XMLFİLTRELE("<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>";"xpath")
şeklinde formüle edilerek, xpath içeriğine göre işlem yapılacaktır.
xpath ile ilgili referansı aşağıdaki bağlantıda görebilirsiniz.
Formülümüzde ise xpath olarak "//b" kullanılmıştır.. Anlamı ise tüm listenin elde edilmesidir.
Bir örnek
Burada isimler arasında Virgül kullanıldığından Virgüllerin yerlerine "</a><a>" konularak XML yapısı elde edildi
xpath olarak "//b" kullanıldı ve liste elde edildi.
Aynı şekilde, xpath olarak "//a[not(. = preceding::a/.)]" kullanılarak BENZERSİZ isimler listesi oluşturulmakta...
Bir örnek daha
Burada ise, xpath olarak "/x/a[11]" kullanılarak A1 de bulunan ifadenin 11. kelimesi alınmakta...
Formülümüzdeki METİNBİRLEŞTİR işlevi ise XML yapısı elde edilmek için kullanıldı...
METİNBİRLEŞTİR işlevinin özelliği, her bir metin ayrı ayrı yazılarak, ya da formül kullanarak metinleri birleştirmektir. Bu işlevde Sayfa1 : Sayfa5!A5:C15 gibi 3D formülleri kullanılabilmektedir.
Formülümüzde
XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b")
kısmı ile elde edilen liste ise aşağıdaki şekildedir....
Bu listenin 1. satırından itibaren her 3 satırda bir olanlar Beden numarasını vermekte... Yani formülümüzdeki (SATIRSAY($A$1:A1)-1)*3+1) ile
1, 4, 7.... gibi 1. satırından itibaren her 3 satırda bir olanların liste satır numaraları bulunmakta.... Ve
İNDİS(Liste;(SATIRSAY($A$1:A1)-1)*3+1)) ile de Bu listenin istenilen satır numaralarına karşılık gelen isimler yazdırılmaktadır....
Umarım açıklayabilmişimdir....
Saygılarımla...
Beden Kodlarını listelemek için kullandığımız
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+1);"")
formülünde,
XMLFİLTRELE işlevi ana işlev olup, XMLFİLTRELE işlevi belirtilen xpath'i kullanarak XML içeriğinden belirli verileri döndürür. Buradaki yapı standarttır.
Yani
Bunun için ilk önce, yazılan metinleri geçerli bir XML e dönüştürmek gerekir.
Örneğin A1 hücresinde sadece Boşluklar kullanarak yazılmış bir cümle olsun.
"<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>")
ile A1 hücresinde kelimeler arasındaki boşlukları "</a><a>" yaparak cümle içindeki kelimeleri içeren XML yapısı elde edilir.
XMLFİLTRELE ile
XMLFİLTRELE("<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>";"xpath")
şeklinde formüle edilerek, xpath içeriğine göre işlem yapılacaktır.
xpath ile ilgili referansı aşağıdaki bağlantıda görebilirsiniz.
Formülümüzde ise xpath olarak "//b" kullanılmıştır.. Anlamı ise tüm listenin elde edilmesidir.
Bir örnek
Burada isimler arasında Virgül kullanıldığından Virgüllerin yerlerine "</a><a>" konularak XML yapısı elde edildi
xpath olarak "//b" kullanıldı ve liste elde edildi.
Aynı şekilde, xpath olarak "//a[not(. = preceding::a/.)]" kullanılarak BENZERSİZ isimler listesi oluşturulmakta...
Bir örnek daha
Burada ise, xpath olarak "/x/a[11]" kullanılarak A1 de bulunan ifadenin 11. kelimesi alınmakta...
Formülümüzdeki METİNBİRLEŞTİR işlevi ise XML yapısı elde edilmek için kullanıldı...
METİNBİRLEŞTİR işlevinin özelliği, her bir metin ayrı ayrı yazılarak, ya da formül kullanarak metinleri birleştirmektir. Bu işlevde Sayfa1 : Sayfa5!A5:C15 gibi 3D formülleri kullanılabilmektedir.
Formülümüzde
XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b")
kısmı ile elde edilen liste ise aşağıdaki şekildedir....
Bu listenin 1. satırından itibaren her 3 satırda bir olanlar Beden numarasını vermekte... Yani formülümüzdeki (SATIRSAY($A$1:A1)-1)*3+1) ile
1, 4, 7.... gibi 1. satırından itibaren her 3 satırda bir olanların liste satır numaraları bulunmakta.... Ve
İNDİS(Liste;(SATIRSAY($A$1:A1)-1)*3+1)) ile de Bu listenin istenilen satır numaralarına karşılık gelen isimler yazdırılmaktadır....
Umarım açıklayabilmişimdir....
Saygılarımla...
Moderatörün son düzenlenenleri:
Detaylı anlatımınız için çok teşekkür ederim. Dogruyu söylemek gerekirse anlattıklarınız benim excel seviyemin üzerinde ve saatlerdir hem araştırıp hem de sizin yazdıklarınızı tekrar tekrar okuyarak formül içindeki fonksiyonların işlevlerini kullanabilecek şekilde ögrenmeye çalışıyorum.Selamlar,
Beden Kodlarını listelemek için kullandığımız
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b");(SATIRSAY($A$1:A1)-1)*3+1);"")
formülünde,
XMLFİLTRELE işlevi ana işlev olup, XMLFİLTRELE işlevi belirtilen xpath'i kullanarak XML içeriğinden belirli verileri döndürür. Buradaki yapı standarttır.
Yani
Bunun için ilk önce, yazılan metinleri geçerli bir XML e dönüştürmek gerekir.
Örneğin A1 hücresinde sadece Boşluklar kullanarak yazılmış bir cümle olsun.
"<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>")
ile A1 hücresinde kelimeler arasındaki boşlukları "</a><a>" yaparak cümle içindeki kelimeleri içeren XML yapısı elde edilir.
XMLFİLTRELE ile
XMLFİLTRELE("<Metin><a>"&YERİNEKOY(A1;" ";"</a><a>")&"</a></Metin>";"xpath")
şeklinde formüle edilerek, xpath içeriğine göre işlem yapılacaktır.
xpath ile ilgili referansı aşağıdaki bağlantıda görebilirsiniz.
Formülümüzde ise xpath olarak "//b" kullanılmıştır.. Anlamı ise tüm listenin elde edilmesidir.
Bir örnek
Burada isimler arasında Virgül kullanıldığından Virgüllerin yerlerine "</a><a>" konularak XML yapısı elde edildi
xpath olarak "//b" kullanıldı ve liste elde edildi.
Ekli dosyayı görüntüle 14818
Aynı şekilde, xpath olarak "//a[not(. = preceding::a/.)]" kullanılarak BENZERSİZ isimler listesi oluşturulmakta...
Bir örnek daha
Burada ise, xpath olarak "/x/a[11]" kullanılarak A1 de bulunan ifadenin 11. kelimesi alınmakta...
Ekli dosyayı görüntüle 14820
Formülümüzdeki METİNBİRLEŞTİR işlevi ise XML yapısı elde edilmek için kullanıldı...
METİNBİRLEŞTİR işlevinin özelliği, her bir metin ayrı ayrı yazılarak, ya da formül kullanarak metinleri birleştirmektir. Bu işlevde Sayfa1 : Sayfa5!A5:C15 gibi 3D formülleri kullanılabilmektedir.
Formülümüzde
XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'B1:B10'!$A$2:$C$1000)&"</b></a>";"//b")
kısmı ile elde edilen liste ise aşağıdaki şekildedir....
Ekli dosyayı görüntüle 14821
Bu listenin 1. satırından itibaren her 3 satırda bir olanlar Beden numarasını vermekte... Yani formülümüzdeki (SATIRSAY($A$1:A1)-1)*3+1) ile
1, 4, 7.... gibi 1. satırından itibaren her 3 satırda bir olanların liste satır numaraları bulunmakta.... Ve
İNDİS(Liste;(SATIRSAY($A$1:A1)-1)*3+1)) ile de Bu listenin istenilen satır numaralarına karşılık gelen isimler yazdırılmaktadır....
Umarım açıklayabilmişimdir....
Saygılarımla...
Fakat aşağıdaki dosyada kullanabilecek şekilde formülü revize etmeyi başaramadım.Bir önceki paylaştığım dosyada hücreler farklı olduğu için hata veya yanlış veri aldım.
İsteğim, aynı şekilde beden barkodlarını, Renk , Beden ve Adetlerini Sheet1 sayfasına çekmek.
Bu konuda da yardımcı olur formülü paylaşırsanız çok mutlu olurum
Bu şekilde bana gönderdiğiniz 1. ve 2. formülleri kıyaslayarak, en azından revize edebilecek şekilde anlamlandırabilmeyi deneyeceğim.
Örnek dokumanın, gerçeği yansıtmaması problemi nedeniyle
formüllerde bazı değişiklikler yapmamız gerekmekte....
O nedenle, aşağıdaki olayların netleşmesi gerekmekte...
Formüller bu olayların netliğine göre yeniden düzenlenmeli....
1 - Veriler her sayfada A18 : E22 arasında...
2 - Her sayfanın 22. satırlarında Veriler YOK.... (Bu ÖNEMLİ) ... Belki dolabilir mi...? Yani bazı sayfaların 22. satırında Veri olup, bazılarında olmayabilir mi? Ya da bazı sayfalarda sadece 18. satırda veri vardır da 22. satıra kadar Boştur.... vs gibi...
3 - Alış Fiyatı listelerinizde 3. kolonda... Evet Veriler YOK. Şu haliyle XMLFİLTRELE de o satırlar olmadan listelenecek. Ancak Veri Yazıldığında listelemeleriniz yanlış olacak. Burada veriler bulunabilecek mi?
4 - Buradan şunu da ekleyebiliriz... Beden kod numarası varken, diğer kriterler için bilgiler yazılmayıp BOŞ da bırakılabildiği durumlar da olabilir.
Örneğin, Bir Beden Kod numarası için Renk kolonuna bir şey yazılmayabilir, ya da Beden kısmı BOŞ bırakılmış olabilir.
Bu mümkün müdür?
5 - Sayfalarda TEKRAR eden beden Kod Numaraları olabilir mi? Yani, örneğin RB5 sayfasında olan Bir Beden Kod Numarası, RB16 sayfasında da bulunabilir mi?
6 - Sheet1 de listeleme yaparken, aralarda BOŞ satırlar olabilir mi? Örneğin ilk sayfada RB1 sayfasında 18 - 21. satırlar dolu 22. satır BOŞ ... Listelemede 4 adet kod numarası + 1 BOŞ şeklinde mi olacak yoksa BOŞ yerine RB2 sayfasının ilk DOLU değeri mi gelecek... Eğer öyle ise formülün İNDİS kısmında köklü bir değişiklik gerekecek...
formüllerde bazı değişiklikler yapmamız gerekmekte....
O nedenle, aşağıdaki olayların netleşmesi gerekmekte...
Formüller bu olayların netliğine göre yeniden düzenlenmeli....
1 - Veriler her sayfada A18 : E22 arasında...
2 - Her sayfanın 22. satırlarında Veriler YOK.... (Bu ÖNEMLİ) ... Belki dolabilir mi...? Yani bazı sayfaların 22. satırında Veri olup, bazılarında olmayabilir mi? Ya da bazı sayfalarda sadece 18. satırda veri vardır da 22. satıra kadar Boştur.... vs gibi...
3 - Alış Fiyatı listelerinizde 3. kolonda... Evet Veriler YOK. Şu haliyle XMLFİLTRELE de o satırlar olmadan listelenecek. Ancak Veri Yazıldığında listelemeleriniz yanlış olacak. Burada veriler bulunabilecek mi?
4 - Buradan şunu da ekleyebiliriz... Beden kod numarası varken, diğer kriterler için bilgiler yazılmayıp BOŞ da bırakılabildiği durumlar da olabilir.
Örneğin, Bir Beden Kod numarası için Renk kolonuna bir şey yazılmayabilir, ya da Beden kısmı BOŞ bırakılmış olabilir.
Bu mümkün müdür?
5 - Sayfalarda TEKRAR eden beden Kod Numaraları olabilir mi? Yani, örneğin RB5 sayfasında olan Bir Beden Kod Numarası, RB16 sayfasında da bulunabilir mi?
6 - Sheet1 de listeleme yaparken, aralarda BOŞ satırlar olabilir mi? Örneğin ilk sayfada RB1 sayfasında 18 - 21. satırlar dolu 22. satır BOŞ ... Listelemede 4 adet kod numarası + 1 BOŞ şeklinde mi olacak yoksa BOŞ yerine RB2 sayfasının ilk DOLU değeri mi gelecek... Eğer öyle ise formülün İNDİS kısmında köklü bir değişiklik gerekecek...
Sorularımdan 5 no lu maddede yazdığım TEKRAR edilebilirlik de, Tekrar etse bile Aynı Kod No için Beden, Renk değişikliği olmayacağını düşünüyorum... Sadece adetleri farklı olabilir. Onlar için de toplam alınabilir....
Adet toplam olayını şimdilik dikkate almazsak,
Sheet A2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'RB1:RB25'!$A$18:$A$22)&"</b></a>";"//b");SATIRSAY($A$1:A1));"")
B2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+1))
C2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+3))
D2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+4))
formüllerini yazın ve
A2 : D2 seçip aşağıya doğru kopyalayın.....
Not : İngilizce
EĞER : IF
İNDİS : INDEX
EĞERHATA : IFERROR
XMLFİLTRELE : FILTERXML
METİNBİRLEŞTİR : JOINTEXT
KAÇINCI : MATCH
SATIRSAY : ROWS
Sorularımdan 1, 2, 3, 4 ve 6 dikkate alındı....
5. sorumdaki olayı söylemiştim.
Adet toplam olayını şimdilik dikkate almazsak,
Sheet A2 hücresine
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'RB1:RB25'!$A$18:$A$22)&"</b></a>";"//b");SATIRSAY($A$1:A1));"")
B2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+1))
C2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+3))
D2 hücresine
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+4))
formüllerini yazın ve
A2 : D2 seçip aşağıya doğru kopyalayın.....
Not : İngilizce
EĞER : IF
İNDİS : INDEX
EĞERHATA : IFERROR
XMLFİLTRELE : FILTERXML
METİNBİRLEŞTİR : JOINTEXT
KAÇINCI : MATCH
SATIRSAY : ROWS
Sorularımdan 1, 2, 3, 4 ve 6 dikkate alındı....
5. sorumdaki olayı söylemiştim.
1 - Veriler her sayfada A18 : E22 arasında olacak.
2 - 22.Satırda veri yok ama veri olabilir.
3 - Evet Alış fiyatı bulunabilir.
4 - Evet yazılmaması mümkündür.
5 - Hayır var olan bir barkod numarası diğer sayfalarda bulunamaz. Her kod tek
6 - 4 adet kod ve sonrasında RB2deki kod gelmeli. Boşluk sadece dolduğunda eklenebilmeli
O halde verdiğim çözümlere ilave bir şey yapılması gerekmiyor.
Siz çözümleri deneyin....
Dosyayı ekledim...
Formülleri kısaca açıklamak gerekirse
A kolonunda
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'RB1:RB25'!$A$18:$A$22)&"</b></a>";"//b");SATIRSAY($A$1:A1));"")
** XMLFİLTRELE ile Sayfaların sadece A18:A22 aralığındaki Beden Kod Numaraları listesi elde edildi
** METİNBİRLEŞTİR işlevinde "</b><b>" ibaresinden hemen sonra gelen Noktalı virgülden sonra Varsayılan değer olan 1 olduğundan BOŞ bırakıldı.. İki Noktalı Virgül arasına 1 de yazılabilir. 1 olması durumu : BOŞ değerlerin dikkate alınmadan listelenmesi anlamındadır.
** Sadece beden Kod numaraları listesi olduğundan SATIRSAY($A1:A1) kullanıldı.
** İNDİS(Liste;SATIRSAY($A$1:A1)) ile listeleme yapıldı.
** EĞERHATA ile de XMLFİLTRELE listesinin adedinden fazla satırlar için BOŞ("") yazdırılması sağlandı.
Diğer kolonlar için
Örneğin Renk için
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+1))
** Aynı satırdaki Beden Kod Numarası formülde referans alındı.
** Beden Kod Numarası BOŞ("") ise BOŞ("") yazılıyor.
** Burada önemli olan
XMLFİLTRELE işlevinde değerlendirilecek aralık sadece A18 : A22 aralığı değil 'RB1:RB25'!$A$18:$E$22 olduğu gibi A18 : E22 aralığıdır.
METİNBİRLEŞTİR işlevinde "</b><b>" ibaresinden hemen sonra gelen Noktalı virgülden sonra ise 0 yazıldı.. 0 olması durumu : BOŞ değerlerin de dikkate alınması ile listelenmesi anlamındadır. BOŞ değerler dikkate alındığında #DEĞER hatası verecektir. Hem bu hatayı vermemesi ve hem de Sheet1 de listelemede BOŞ yerine 0 yazmaması açısından METİNBİRLEŞTİR işlevi EĞERHATA işlevine sarıldı ve BOŞ("") yazdırılması sağlandı.
Budurumda XMLFİLTRELE ile şöyle liste elde edildi. Her 5 li grubun (yani ilk 5 satır, ikinci 5 satır, üçüncü 5 satır.... gibi)
1. satır : beden Kod Numarası
2. satır : Renk
3. satır : Alış Fiyatı
4. satır : Beden
5. satır : Adet
** A kolonunda bulunan Beden Kod Numarası XMLFİLTRELE ile elde edilen listede kaçıncı olduğuna bakılıyor. Bulunan değerin bir sonrası Renk, 3 sonrası Beden ve 4 sonrası ise adet olarak bulunuyor.
A kolonunda
=EĞERHATA(İNDİS(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";;'RB1:RB25'!$A$18:$A$22)&"</b></a>";"//b");SATIRSAY($A$1:A1));"")
** XMLFİLTRELE ile Sayfaların sadece A18:A22 aralığındaki Beden Kod Numaraları listesi elde edildi
** METİNBİRLEŞTİR işlevinde "</b><b>" ibaresinden hemen sonra gelen Noktalı virgülden sonra Varsayılan değer olan 1 olduğundan BOŞ bırakıldı.. İki Noktalı Virgül arasına 1 de yazılabilir. 1 olması durumu : BOŞ değerlerin dikkate alınmadan listelenmesi anlamındadır.
** Sadece beden Kod numaraları listesi olduğundan SATIRSAY($A1:A1) kullanıldı.
** İNDİS(Liste;SATIRSAY($A$1:A1)) ile listeleme yapıldı.
** EĞERHATA ile de XMLFİLTRELE listesinin adedinden fazla satırlar için BOŞ("") yazdırılması sağlandı.
Diğer kolonlar için
Örneğin Renk için
=EĞER($A2="";"";İNDİS(EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");KAÇINCI($A2;EĞERHATA(XMLFİLTRELE("<a><b>"&METİNBİRLEŞTİR("</b><b>";0;'RB1:RB25'!$A$18:$E$22)&"</b></a>";"//b");"");0)+1))
** Aynı satırdaki Beden Kod Numarası formülde referans alındı.
** Beden Kod Numarası BOŞ("") ise BOŞ("") yazılıyor.
** Burada önemli olan
XMLFİLTRELE işlevinde değerlendirilecek aralık sadece A18 : A22 aralığı değil 'RB1:RB25'!$A$18:$E$22 olduğu gibi A18 : E22 aralığıdır.
METİNBİRLEŞTİR işlevinde "</b><b>" ibaresinden hemen sonra gelen Noktalı virgülden sonra ise 0 yazıldı.. 0 olması durumu : BOŞ değerlerin de dikkate alınması ile listelenmesi anlamındadır. BOŞ değerler dikkate alındığında #DEĞER hatası verecektir. Hem bu hatayı vermemesi ve hem de Sheet1 de listelemede BOŞ yerine 0 yazmaması açısından METİNBİRLEŞTİR işlevi EĞERHATA işlevine sarıldı ve BOŞ("") yazdırılması sağlandı.
Budurumda XMLFİLTRELE ile şöyle liste elde edildi. Her 5 li grubun (yani ilk 5 satır, ikinci 5 satır, üçüncü 5 satır.... gibi)
1. satır : beden Kod Numarası
2. satır : Renk
3. satır : Alış Fiyatı
4. satır : Beden
5. satır : Adet
** A kolonunda bulunan Beden Kod Numarası XMLFİLTRELE ile elde edilen listede kaçıncı olduğuna bakılıyor. Bulunan değerin bir sonrası Renk, 3 sonrası Beden ve 4 sonrası ise adet olarak bulunuyor.
Hocam elinize emeğinize sağlık, istediğim şekilde çıkarabiliyorum artık verileri
Çok yardımcı oldunuz, çok teşekkür ederim
Ben teşekkür ediyorum...
Yardımcı olabildiğime inanın çok sevindim...
Saygılarımla...
Yardımcı olabildiğime inanın çok sevindim...
Saygılarımla...
@brkayahan
Ben de makro ile çözüm için öneride bulunayım bari.
Şu kod işinizi görür.
Sheet1 dışındaki tüm sayfalarda 18'inci satırdan itibaren (aşağı doğru ilk boş hücreye rastlandığında o sayfa için işlem kesilir)
yer alan bilgiler Sheet1'e listelenir.
Ben de makro ile çözüm için öneride bulunayım bari.
Şu kod işinizi görür.
Sheet1 dışındaki tüm sayfalarda 18'inci satırdan itibaren (aşağı doğru ilk boş hücreye rastlandığında o sayfa için işlem kesilir)
yer alan bilgiler Sheet1'e listelenir.
VBA:
Görüntülemek için giriş yapmanız gerekmektedir.
(17 satır)@Ömer BARAN Makro bilgisi için teşekkür ederim
Çalışırken bir ihtiyaç daha belirdi
Dosyadaki yeşil ve kırmızı alandan Sheet1 sayfasında bulunan Kumaş Bilgisi ve Örme/Dokuma alanlarına veri çekmem gerekiyor.
Örme/Dokuma Bilgisi(Yeşil Hücre ):
1 - Veriler her sayfada B7 hücresinde
2 - B7 hücresinde olan veri bulunduğu sayfadaki bütün ürün barkodlarının ortak özelligidir. Ürün Barkoduna bağlı olarak çekilmeli (temel amacım Ürün barkodunun hangi örme/dokuma bilgisine sahip olduğunu çekmek )
3 - B7 hücresinde bulunan veri değişkendir.(Örme veya Dokuma dışında farklı bilgi de yer alabilir.
4 - Bu hücrede veri olmayabilir. Olmadığında boş olarak göstermeli
5 - B7 hücresinde bulunan veri diğer sayfalarda da aynı şekilde bulunabilir
Kumaş Bilgisi ( Kırmızı Hücreler ):
1 - 2 veri de her sayfada 25. satırdaki, B ile C'nin ve D,E,F nin birleştiği hücrede
2 - 2 hücreyi aralarına "/" ekleyip, Slash'ın iki tarafına birer boşluk gelecek şekilde birleştirerek çekmem gerekiyor. ( %100 Pamuk / %100 Cotton )
3 - Kırmızı hücrede olan veri bulunduğu sayfadaki bütün ürün barkodlarının ortak özelligidir. Ürün Barkoduna bağlı olarak çekilmeli (temel amacım ürün barkodunun hangi kumaş bilgisine sahip olduğunu çekmek )
4 - Kırmızı hücrede bulunan veriler değişkendir.
5 - Bu hücrede veri olmayabilir. Olmadığında boş olarak göstermeli
6 - Kırmızı hücrelerde bulunan veri diğer sayfalarda da aynı şekilde bulunabilir
Bu konuda da yardımcı olursanız çok sevinirim