• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Transponeren d.m.v. functies

Status
Niet open voor verdere reacties.
M

MrAwesome

Gebruiker
Lid geworden
6 mrt 2015
Berichten
36
Goedemiddag,

Ik wil een importbestand maken voor een rapportageprogramma, maar dit moet in Excel gebeuren. Nou is de data die ik heb horizontaal weergegeven (zie voorbeeld 1, eerste werkblad). Ik moet dit alleen verticaal importeren, dus heb ik dit geprobeerd op te lossen d.m.v. de functie Transponeren en een matrix formule (zie voorbeeld 1, tweede werkblad, gele cellen). Hieruit vloeien twee vragen voort:

1. Ik wil het importeren zoals het in de groene cellen staat (voorbeeld 1, tweede werkblad, groene cellen). Weet iemand hoe ik dit gemakkelijk kan doen? Denk eraan dat er in de gele kolom ook nulregels staan (kolom E). Dit heeft ermee te maken met dat de bron een sjabloon is. Dus bij de ene klant staat hier wel een naam maar bij de andere klant weer niet omdat er minder personeel aanwezig is. Wellicht dat ik deze regels er op het laatste moment wel gewoon simpel uit kan filteren.

2. De oplossing die ik nu heb (dus Transponeren & matrix formule) geeft mij niet de mogelijkheid om de sheet te kopiëren naar een ander bestand. Wanneer ik dit doe krijg ik de waardes van Voorbeeld 1. Zie ook Voorbeeld 2, tweede werkblad.

Hopelijk heeft iemand een oplossing.

Gr. :thumb:

Bekijk bijlage Voorbeeld 1.xlsx
Bekijk bijlage Voorbeeld 2.xlsx
 
Vraag2 is eenvoudig op te lossen door te kopieren als waarde (values) te kiezen. Voor vraag 1 heb ik geen oplossing.
 
Peter B zei:
Vraag2 is eenvoudig op te lossen door te kopieren als waarde (values) te kiezen. Voor vraag 1 heb ik geen oplossing.
Klik om te vergroten...

Niet helemaal, want zoals je in Voorbeeld2 ziet, bevat het eerste werkblad weer andere waardes dan Voorbeeld1. Ik wil dezelfde formule toepassen als Voorbeeld1 heeft maar omdat het een matrixformule is, kan ik niet zomaar de formule kopiëren.
 
OK. Het was mij dan dus niet helemaal duidelijk wat je bedoelde met "de sheet kopiëren".

Dan heb ik ook hier geen oplossing voor ...
 
in het "Hulp macro"bestand zit een knop om de macro te starten.

de macro vraagt eerst om de weeknummers en de werkuren (in het voorbeeld bestand zijn dat de grijze cellen)
als je 1 of 2 extra kolommen selecteert krijg je in het eind resultaat ook tussen de overgangen extra open rijen.

daarna vraagt de macro om de namenkolom te selecteren (dat zijn in het voorbeeld bestand de gele cellen)

daarna vraagt de macro naar de waar het resultaat heen moet ( de eerste cel van waar het resultaat heen moet )
dat mag ook een ander bestand zijn.
 

Bijlagen

  • Hulp macro.xlsm
    23,2 KB · Weergaven: 33
  • Voorbeeld input met kleuren..xlsx
    12,6 KB · Weergaven: 30
zie bijlage
 

Bijlagen

  • __vertikaal snb.xlsx
    14,7 KB · Weergaven: 35
Iets eenvoudiger?
Code: 
Sub VenA()
ar = Blad1.Cells(4, 1).CurrentRegion
  Set d = CreateObject("scripting.dictionary")
  For j = 3 To UBound(ar)
    For jj = 5 To 57
      If ar(j, jj) <> "" Then d.Item(ar(2, jj) & "|" & ar(j, 1) & "|" & ar(j, jj)) = ""
    Next jj
  Next j
  
  With Workbooks.Add.Sheets(1)
    .Cells(1).Resize(d.Count) = Application.Transpose(d.keys)
    .Columns(1).TextToColumns , 1, , , , , , , True, "|"
  End With
End Sub
 

Bijlagen

  • Voorbeeld input met kleuren..xlsb
    18,4 KB · Weergaven: 37
Code: 
Sub M_snb()
    sn = Blad1.Cells(4, 1).CurrentRegion
    ReDim sp((UBound(sn) - 2) * (UBound(sn, 2) - 5), 3)
    
    For j = 0 To UBound(sp) - 1
      sp(y, 0) = j Mod 53 + 1
      sp(y, 1) = sn(j \ 53 + 3, 1)
      sp(y, 2) = sn(j \ 53 + 3, j Mod 53 + 5)
      y = y - (sp(y, 2) <> "")
    Next
    
    Blad2.Cells(1).Resize(y, 3) = sp
End Sub
 
Laatst bewerkt:
Bedankt! Ik heb (nog) te weinig verstand van de VBA om nu even snel te kunnen beoordelen of dit in mijn sheets gaat werken. Moet namelijk het script enigszins aanpassen. Kom er later deze week op terug of jullie voorstellen hebben gewerkt :)
 
VenA zei:
Iets eenvoudiger?
Code: 
Sub VenA()
ar = Blad1.Cells(4, 1).CurrentRegion
  Set d = CreateObject("scripting.dictionary")
  For j = 3 To UBound(ar)
    For jj = 5 To 57
      If ar(j, jj) <> "" Then d.Item(ar(2, jj) & "|" & ar(j, 1) & "|" & ar(j, jj)) = ""
    Next jj
  Next j
  
  With Workbooks.Add.Sheets(1)
    .Cells(1).Resize(d.Count) = Application.Transpose(d.keys)
    .Columns(1).TextToColumns , 1, , , , , , , True, "|"
  End With
End Sub
Klik om te vergroten...

Inmiddels zijn we een maandje verder waarbij ik in mijn vrije tijd heb geprobeerd om de macro's te bestuderen, dus de basics, maar de sheet van VenA snap ik nog niet helemaal.

Wat uitleg m.b.v. een apostrof in de macro zou al een hoop schelen. Een aantal zaken begrijp ik, maar het merendeel toch niet. Kan iemand me wijzer maken? :shocked:

Greetzzz
 
Het quoten is niet nodig. Werkt het wel of niet?
Je kan met de ingebakken hulpmiddelen in de VB editor zoals <F1> (help), <F8> (step) het meest wel vinden/volgen. Daarnaast kan je het internet gebruiken om meer te lezen over het gebruik van array's en dictionary's. Klik maar eens op het rode linkje in de handtekening van @snb.
 
Ha VenA,

Dank voor de reactie. De oplossing werkte. Ik zal de topic dan ook markeren als opgelost. Het internet heb ik de afgelopen weken al doorgezocht op diverse info over macro's maar het is nogal veel. Dank voor jouw tips m.b.t. het rode linkje en de F1/F8 keys.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan