Draaitabel met meerdere gegevens naar platte tabel

[Matthijsm]

Ik heb een vraag waarvan ik nog niet duidelijk op een ander draadje het antwoord heb kunnen vinden.

Ik ben bezig met een kennisniveau tabel voor medewerkers. Ik wil per medewerker kunnen aangeven wat zijn niveau is voor een bepaald soort werkzaamheden. Daarnaast gaat het ook om vele verschillende soorten installaties. Die installaties zijn wel gegroepeerd. Voor het invullen moet ik een soort rapportcijfer geven van 1 t/m 5. Uiteindelijk moet ik het als platte tabel in CSV formaat kunnen inlezen in het systeem waar we mee werken.

Dus voorbeeld... zoals in de bijlage.

Ik heb 4 medewerkers, Piet, Klaas, Kees en Henk. Die werken aan verschillende soorten installaties die een code hebben gekregen 11, 12 ,13, 21, 22 etc. Sommige installaties lijken op elkaar dus daar is een groep van, bijv. 100 en 200. Ik wil eenvoudig kunnen invullen dat bijv. Piet voor de installaties in groep 100 qua kennisniveau op een 5 zit. Dan moet bij 11, 12 en 13 een 5 komen te staan. Stel dat voor categorie 13 hij maar een 3 krijgt, moet er daar apart een 3 in gevuld kunnen worden. Echter vallen de werkzaamheden van de verschillende installaties ook nog te verdelen in 4 verschillende werkzaamheden. Werksoort A t/m D om het makkelijk te houden.

Uiteindelijk moet ik het compleet ingevulde inlezen. Daarbij moet iedere regel een eigen unieke ID krijgen. Dat ID lijkt me makkelijk te bedenken door bijv. een combinatie van de vragen te maken. Bijv.
Piet.10.werksoortA antwoord: 3
Piet.11.werksoortA antwoord: 1
Piet.21.werksoortB antwoord: 5

enz enz.

Dat wordt alleen een hele lange lijst, dus daarom dacht ik het in een draai tabel te maken om eenvoudiger te kunnen invullen. Met name via die groepering, zodat als alle soorten in de groep hetzelfde zijn, je maar 1 cijfer hoeft te geven.

Ik loop alleen even vast hoe ik iets zal bouwen. Wie kan me helpen?

 

[VenA]

Kan je in het uitermate kale voorbeeld de verwachte resultaten even handmatig aangeven. Waar zijn de werksoorten te vinden? Waar zijn de cijfers te vinden?

 

[AlexCEL]

Je maakt meestal een draaitabel van een platte tabel, niet andersom...

Het klinkt heel erg als een database wat je zoekt.

 

[Matthijsm]

Kan je in het uitermate kale voorbeeld de verwachte resultaten even handmatig aangeven. Waar zijn de werksoorten te vinden? Waar zijn de cijfers te vinden?

Ik heb het voorbeeld iets aangevuld. Stele je vult bij 100 in dat iemand voor die groep een 5 scoort, moet bij 11, 12 en 13 een 5 komen. Dat kan natuurlijk simpel gewoon overschreven worden door een ander getal, logisch. Ik heb de verschillende soorten werkzaamheden A, B, C, D nog nergens ingevoerd, ik heb namelijk even geen idee hoe ik dit nog kan verwerken in een tabel als dit. Misschien moet ik de vraag dus even opsplitsen:

- Hoe transformeer ik deze draaitabel naar een platte tabel waarbij alleen de cijfers 11, 12 en 13 bijvoorbeeld meegenomen worden maar niet de groepsnummers.
- Hoe zorg ik dat er per antwoordregel (zie blad 2) eventueel automatisch een ID wordt gemaakt op basis van de rij / kolom koppen
- Hoe kan ik per categorie zoals 11, 12 en 13 ook nog een extra soort toevoegen. Dus categorie 11 werkzhd A, categorie 11 werkzhd B enz.

N.b. Als het uiteindelijk af is zou het mooiste zijn als het toekomst bestendig is en ik het eenvoudig kan uitbreiden als er een nieuwe medewerker bij komt.

 

[VenA]

Dat zal dan wel een macro worden.

2 opties in het bestand.

Sub VenA()
  ar = Sheets("Blad1").Cells(1).CurrentRegion
  ReDim ar1(3, 0)
  For j = 3 To UBound(ar)
    For jj = 2 To UBound(ar, 2)
      If ar(1, jj) <> "" Then
        c00 = ar(1, jj)
       Else
        ar1(0, t) = ar(j, 1)
        ar1(1, t) = c00
        ar1(2, t) = ar(2, jj)
        ar1(3, t) = ar(j, jj)
        t = t + 1
        ReDim Preserve ar1(3, t)
      End If
    Next jj
  Next j
  
  With Sheets("Blad2")
    .Cells.Clear
    .Cells(1).Resize(, 4) = Array("Naam", "Groep", "Subgroep", "Cijfer")
    .Cells(2, 1).Resize(t, 4) = Application.Transpose(ar1)
  End With
End Sub

Sub VenA1()
  Set d = CreateObject("Scripting.Dictionary")
  ar = Sheets("Blad1").Cells(1).CurrentRegion
  For j = 3 To UBound(ar)
    For jj = 2 To UBound(ar, 2)
      If ar(1, jj) = "" Then d(ar(j, 1) & "_" & ar(2, jj)) = ar(j, jj)
    Next jj
  Next j
  
  With Sheets("Blad2")
    .Cells.Clear
    .Cells(1).Resize(, 2) = Array("Id", "Cijfer")
    .Cells(2, 1).Resize(d.Count, 2) = Application.Transpose(Array(d.keys, d.items))
  End With
End Sub

 

[Matthijsm]

Wauw, dat komt al heel snel behoorlijk in de buurt van wat ik zoek, bedoel en wil hebben. Tof! Ik ben zelf wel de kluts kwijt wat de code betreft ;-)

Ik denk dat de 2e macro (met de titel VenA1) voor mij de beste optie is, dan genereert ie namelijk meteen een ID dat ik goed kan gebruiken.

Voor wat betreft de 4 soorten werkzaamheden kan ik wellicht het beste gewoon 4 bestanden maken. Dat is misschien het makkelijkste. Of heb je wellicht ook een goed, misschien eenvoudige toevoeging, waardoor ik nog een extra laag kan toevoegen. Ik heb om het misschien iets duidelijker te maken wat ik bedoel het bestand aangevuld. Het idee blijft dat ik bij GROEPSTOTAAL iets invul en dit dan over de hele groep geplaatst wordt. Of bij TOTAAL iets invul en het op het hele regeltje komtvoor alleen die 'subgroep', tenzij je dat handmatig aanpast. Resultaat zou dus dan zijn:
- Klaas.11.A: 5
- Klaas.12.A: 5
- Klaas.13.A: 2
....
....
- Klaas 11.B: 5
....
- Klaas 13.C: 2

enz. enz.

 

[VenA]

Al je eerst die onnodige quote weghaalt en de code(s) in het bestand zet dan wil ik er misschien wel naar kijken.

 

[Matthijsm]

Quote weggehaald, excuus.

Bij deze het bestand met de macro toegevoegd. Ben er niet heel erg mee bekend, dus hoop dat ik het goed heb gedaan. Ook even de formules er in gezet.

 

[VenA]

Heb je zelf wel op jouw netvlies staan van wat je eigenlijk wilt? Al die samengevoegde cellen en tussen kolommen maakt de code alleen maar onnodig ingewikkeld.

Probeer maar weer

Sub VenA1()
  Set d = CreateObject("Scripting.Dictionary")
  ar = Sheets("Blad1").Cells(1).CurrentRegion
  For j = 4 To UBound(ar)
    For jj = 4 To UBound(ar, 2)
      If ar(3, jj) <> "" Then
        If InStr(1, ar(3, jj), "totaal", vbTextCompare) Then
          c00 = ar(2, jj)
         Else
          d(ar(j, 1) & "_" & ar(3, jj) & "_" & c00) = ar(j, jj)
        End If
      End If
    Next jj
  Next j
  
  With Sheets("Blad2")
    .Cells.Clear
    .Cells(1).Resize(, 2) = Array("Id", "Cijfer")
    .Cells(2, 1).Resize(d.Count, 2) = Application.Transpose(Array(d.keys, d.items))
  End With
End Sub

 

[Matthijsm]

Top, dit lukt ook. Dankje ik ga weer even verder stoeien. Nogmaals dank!

P.s. Ja wel wat ik in m'n hoofd heb. Maar liep dus klem met hoe ik het het beste kon beginnen en in wat voor vorm ik de tabel het beste kon/kan maken. Vandaar de hulpvraag ook. Mocht je een handiger idee hebben dan is die alsnog meer dan welkom

 

[VenA]

Heb je de code wel getest? Je moet eigenlijk 128 rijen krijgen ipv 64. Maar in groep 200 begin je ook weer met 11 ipv 21. Het is allemaal nogal ondoordacht wat je hier plaatst.

Een handiger idee heb ik niet behalve het in 1 keer gestructureerd vast te leggen dmv 1 tabel en een Userform. (maar gaat mij te ver om het uit te werken en er zijn voldoende voorbeelden te vinden over het hoe)

 

[Matthijsm]

Daar was ik net mee bezig en zag inderdaad dat ik even een doorvoerfout had gemaakt.

Had nu getest met meer namen toe te voegen, en dat gaat goed met het maken van de ID's. Het invullen van de resultaten stopt echter wel bij 128.

Bedankt voor de tip.