Bir firmadan aldığımız excel'i makro ya da formüller ile düzenlemem gerekiyor. Çok fazla değişken olduğu için yapılabilirliğini bilmiyorum ama oluyorsa desteğinizi rica ediyorum. Amacım, tek bir satırda verilen değerleri pivot ile seçilebilen alanlara dönüştürmek.
Birinci aşama
Üst başlıklar halinde gelen ürünlerin her birinin bir part no(ürün kodu) bulunmakta. İlk işlem, her bir başlığa ait satırların başına bu ürün başlıklarını ek bir satır olarak eklemek. Aşağıda, örnek bir excel verisi mevcut.
PART NO | DESCRIPTION |
1400 Series | ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model |
14819.5N | Single Installation License fee for 10 Administrators (unrestricted resources and users) and 25 Keys |
14819.5M | Annual Maintenance and Support fee for 10 Administrators (unrestricted resources and users) and 25 Keys |
14829.5N | Single Installation License fee for 20 Administrators (unrestricted resources and users) and 25 Keys |
14829.5M | Annual Maintenance and Support fee for 20 Administrators (unrestricted resources and users) and 25 Keys |
14839.5N | Single Installation License fee for 25 Administrators (unrestricted resources and users) and 25 Keys |
14839.5M | Annual Maintenance and Support fee for 25 Administrators (unrestricted resources and users) and 25 Keys |
14849.5N | Single Installation License fee for 50 Administrators (unrestricted resources and users) and 25 Keys |
14849.5M | Annual Maintenance and Support fee for 50 Administrators (unrestricted resources and users) and 25 Keys |
14859.5N | Single Installation License fee for 100 Administrators (unrestricted resources and users) and 25 Keys |
14859.5M | Annual Maintenance and Support fee for 100 Administrators (unrestricted resources and users) and 25 Keys |
14869.5N | Single Installation License fee for 150 Administrators (unrestricted resources and users) and 25 Keys |
14869.5M | Annual Maintenance and Support fee for 150 Administrators (unrestricted resources and users) and 25 Keys |
14879.5N | Single Installation License fee for 200 Administrators (unrestricted resources and users) and 25 Keys |
14879.5M | Annual Maintenance and Support fee for 200 Administrators (unrestricted resources and users) and 25 Keys |
PART NO | DESCRIPTION |
1400 Series | ManageEngine PasswordManager Pro Enterprise Edition - Subscription Model |
14819.5S | Annual Subscription fee for 10 Administrators (unrestricted resources and users) and 25 Keys |
14829.5S | Annual Subscription fee for 20 Administrators (unrestricted resources and users) and 25 Keys |
14839.5S | Annual Subscription fee for 25 Administrators (unrestricted resources and users) and 25 Keys |
14849.5S | Annual Subscription fee for 50 Administrators (unrestricted resources and users) and 25 Keys |
Her bir ürün arasında bir boşluk oluyor ve üstünde de "DESCRIPTION" başlığı oluyor. Düzenlemek istediğim ve olması gereken format aşağıdaki gibidir;
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14217.7SL | Annual Subscription fee for 5 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14227.7SL | Annual Subscription fee for 10 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14237.7SL | Annual Subscription fee for 20 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14247.7SL | Annual Subscription fee for 25 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14257.7SL | Annual Subscription fee for 50 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14267.7SL | Annual Subscription fee for 100 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14277.7SL | Annual Subscription fee for 150 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Multi-Language Premium Edition - Subscription Model | 14287.7SL | Annual Subscription fee for 200 Administrators (unrestricted resources and users) |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14819.5N | Single Installation License fee for 10 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14819.5M | Annual Maintenance and Support fee for 10 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14829.5N | Single Installation License fee for 20 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14829.5M | Annual Maintenance and Support fee for 20 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14839.5N | Single Installation License fee for 25 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14839.5M | Annual Maintenance and Support fee for 25 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14849.5N | Single Installation License fee for 50 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14849.5M | Annual Maintenance and Support fee for 50 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14859.5N | Single Installation License fee for 100 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14859.5M | Annual Maintenance and Support fee for 100 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14869.5N | Single Installation License fee for 150 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14869.5M | Annual Maintenance and Support fee for 150 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14879.5N | Single Installation License fee for 200 Administrators (unrestricted resources and users) and 25 Keys |
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14879.5M | Annual Maintenance and Support fee for 200 Administrators (unrestricted resources and users) and 25 Keys |
İkinci Aşama
Bu aşamada yapmak istediğim, bir excel aralığında belirttiğim kelime ya da kelime gruplarının tek tek kontrol edip eğer ilgili sütun ya da sütunlarda bunlardan herhangi birini buluyorsa bulduğu değer ile değerden sonraki değeri bölüp ayrı ayrı sütunlarda bu değerleri girmesi. Yalnız burada da şu sıkıntı var, her bir ürünün bölünecek değer sayısı aynı değil. Yani A ürününün alt kalemi 3'e bölünecekse, B ürününün alt kalemi 5'e bölünebilir.
Örnek satır
ManageEngine PasswordManager Pro Enterprise Edition - Perpetual Model | 14839.5M | Annual Maintenance and Support fee for 25 Administrators (unrestricted resources and users) and 25 Keys | 7.7 |
Olması gereken satır
ManageEngine | PasswordManager Pro | Enterprise Edition | Perpetual Model | Annual Maintenance and Support fee | for 25 Administrators (unrestricted resources and users) | 25 Keys |
Bunları alt alta aynı sütunda bölmesi zor olur çünkü her satırın bölüm adeti farklı olacaktır fakat başka bir sayfaya aktarabilirse yapılabilirliğinin olduğunu düşünüyorum. Bunun mantığını anlarsam, ürün başlıkları dahil birçok alanda kurgulayabileceğimi düşünüyorum.
Bu işlemi, ikinci aşamada oluşturulan her bir satır için yapmalı ve buna göre çıktı alınmalı. Eğer bu şekilde ayırım yapabilirse, pivot'tan düzgün bir şekilde veriler filtrelenip görüntülenebilir ve ürünler isteğe göre özelleştirilebilir.
Desteğiniz için şimdiden teşekkürler