Excel de oluşturulmak istenen yeni tablo ile ilgili soru

[Deniz_Excel]

Merhaba Arkadaşlar,

Excel de yapmak istediğim bir işlem var. Ancak algoritmasını kafamda çıkaramadım.
Elimde olan Excel dosyasını ve işlem sonrası nasıl görünmesini istediğimin ekran görüntüsünü resim olarak ekledim (bu adreste: http://imgim.com/excelscreenshot.png)
Yani kısaca resimden anlayacağınız üzere her bir çalışanın sahip olduğu rol sayısı kadar satır oluşturup karşısında o rolleri görmek istiyorum
Ulaşmak istediğimiz sonuca nasıl bir uygulama sonucu varabiliriz?

(NOT: Eklediğim resimde prototip olarak az sayıda data koydum. İşlemi gerçekleştireceğim excel dosyasında 1700 civarında kullanıcı 10 a yakında rol bulunmakta.)

Çok teşekkürler yardımlar için şimdiden.

 

[Ömer BARAN]

Merhaba.

Ekli dosyayı inceleyiniz.
Belgeye, fareyle buraya tıklayarak da erişebilirsiniz.

 

[Necdet]

Merhaba,

Altenatif olsun, Sayın Ömer BARAN'IN dosyası baz alınarak makro ile düzenlenmiştir.
Listeleme Sayfa2 ye yazılmaktadır.

Sub Duzenle()

    Dim i   As Long, _
        j   As Long, _
        Hcr As Range, _
        s1  As Worksheet, _
        s2  As Worksheet
  
    On Error Resume Next
    Set s1 = Sheets("Sayfa1")
    Set s2 = Sheets("Sayfa2")
  
    s2.Range("A:B").ClearContents
    s2.Range("A1") = "İSİMLER"
    s2.Range("B1") = "ROL"
  
    j = 1
  
    For i = 3 To s1.Cells(Rows.Count, "U").End(3).Row
        For Each Hcr In s1.Range("A" & i & ":T" & i).SpecialCells(xlCellTypeConstants, 23)
            If Err.Number = 0 Then
                j = j + 1
                s2.Cells(j, "A") = s1.Cells(i, "U")
                s2.Cells(j, "B") = s1.Cells(2, Hcr.Column)
            End If
        Next Hcr
    Next i
  
    MsgBox "İŞLEM TAMAMLANMIŞTIR....", vbInformation, "excel.web.tr"
  
End Sub

 

[Ömer BARAN]

Tekrar merhaba.

Gönderdiğim belgedeki formülleri açıklamaya çalışayım..

Y sütunundaki formül ilgili alanda X değerini taşıyan hücrelerin SATIR NUMARALARINI küçükten büyüğe doğru listelemektedir ve diğer formüller de bu değerlerden yararlanıyor.

-- Y sütunu; mevcut verilere göre, değeri X olan hücrelerin 4 tanesi 3'üncü satırda bulunuyor, yani küçüklük sıralamasında 1, 2, 3 ve 4'üncü KÜÇÜK satır numarası 3'tür. 5, 6, 7 ve 8'inci KÜÇÜK satır numaraları ise 4 'tür.......
-- X sütunu; satır numarası Y sütunundaki değer olan satırda hücre değeri X olan sütun numaraları küçükten büyüğe sıralandığında, 1'inci sütun numarası 2 (yani B sütunu), 2'nci KÜÇÜK sütun değeri 5 (E sütunu) ...... bulunur.
-- Kaçıncı KÜÇÜK değerin bulunacağı: Yukarıda belirttiğim 1'inci, 2'nci.... gibi kaçıncı sıradaki değerin bulunacağını ise formüldeki EĞERSAY kısmında karar veriliyor.
-- Bulunan sütun numaralarına göre de KAYDIR işlevi kullanılarak BAŞLIK satırındaki ROL ADLARI bulunuyor.
-- Y sütunundaki değerlere göre de isimler bulunuyor.

 

[Deniz_Excel]

Ömer Baran çok teşekkürler. Ancak şuan şöyle bir durum var. Aşağıya doğru kaydırınca satırları maalesef sonuçları devam ettirmedi. Atmış olduğun dosyada yeni bir satır ekledim mehmet diyerek. Maalesef yeni girileni karşımıza getirmiyor. Formülde bir mantık hatası bulamadım. hatanın neden kaynaklandığını biliyor musun?

Necdet selam makro için teşekkürler. Çalıştırmayı denedim. Ancak

Run time error '9':
Subscript out of range hatası veriyor.

 

[Deniz_Excel]

Bu arada gördüm ki aslında istediğim şeyin tam tersini gerçekleştirmek isteseydik. Pivot table de çok kolay bir uygulama ile bunu elde edebilirdik. REsimdeki gibi karşımıza çıkardı (Ömer Baran'ın dosyası üzerinden gösterim). Pivot table da bu işlemi ters olarak da yapmanın yolu vardır diye düşünüyorum. (yani ilk istediğim)

 

[Deniz_Excel]

Güncelleme@
Arkadaşlar bir önceki Pivot table ile ilgili sorumun cevabını bu videoda buldum(Reverse Pivot). Pivot Wizard dan bunu yapabiliyoruz kolay bir şekilde

Ancak yine de Ömer Baran - formülün neden kaydırınca problem yarattığıyla ilgili merakım devam ediyor.

 

[Necdet]

Necdet selam makro için teşekkürler. Çalıştırmayı denedim. Ancak

Run time error '9':
Subscript out of range hatası veriyor.

Merhaba,

Dosyanızda "Sayfa2" olmalı, hatayı oradan alıyorsunuz. Ayrıca hiç bir rolü olmayan da boş çıkıyordu. Onları da düzelttim ilk mesajımda.

 

[Ömer BARAN]

Ekran görüntüsü eklenen sorulara pek cevap yazmam aslında.
Verdiğim formüllerdeki adres kısımlarını gerçek belgenizdeki yapıya göre güncellemelisiniz.
İşte bu tür sorunlar nedeniyle gerçek belgenin bir kopyası şeklindeki örnek belge istemekteyiz.

Farkındaysanız Sayın Necdet Bey de benim eklediğim belge üzerinden cevap yazmıştı.
Gördüğünüz gibi sorudaki asıl eksiklik örnek begenin olmaması.

Sorularınızı, gerçek belgenizin küçük boyutlu ve özel bilgi içermeyen bir kopyası şeklinde hazırlaycağınız
örnek belge üzerinden sorarsanız daha hızlı sonuca ulaşırsınız.

Örneğin eklediğim belge Y2'deki formülde yer alan ....... KÜÇÜK(EĞER($A$3:$T$5="X";SATIR($A$3:$T$5));SATIR(A1)......
kısmında, verilerin A3:T5 aralığında olduğu varsayılmıştır, ayrıca ilk formül hücresindeki SATIR(A1)'in aynen kalması gerekir.

 

[Deniz_Excel]

Ömer Baran, teşekkürler. yine de faydalı oldunuz. Daha sorularım olacaktır. Açıkcası forumu da aktif kullanmaya yeni başladım. Gelecek sorularda görüşmek dileğiyle.