Opgelost Gegevens uit cellen splitsen en andere gegevens daaraan toevoegen

[Oudholland]

Bij ons op het werk komt er uit een databasesysteem een excel waarin bepaalde gegevens samengevoegd in een bepaalde kolom komen. (ik noem ze in dit voorbeeld "artikelnummers", dat maakt de uitleg wat makkelijker). Met in de rij waarin de samengevoegde cel staat gegevens van de persoon/order waar die artikelnummers aan zijn gekoppeld.
De artikelnummers zijn in de cellen in die kolom steeds gescheiden door een "/". En het brondocument kan tot 300 regels bevatten.
Nu wil ik die gegevens splitsen, en in een nieuw tabblad in (bijvoorbeeld) kolom A die artikelnummers hebben staan (met voor ieder artikel een aparte rij) en daarachter de overige gegevens uit het originele werkblad daarachter in de kolommen.
De "artikelnummers" zullen op het Gegevens-blad steeds maar 1x per stuk voorkomen, en ik wil deze later met verticaal zoeken kunnen vergelijken met een databaselijst van deze artikelnummers (waar ieder artikelnr een aparte rij heeft).

Ik heb een voorbeeld toegevoegd waar ik een paar voorbeeldregels heb gezet, in het format wat door het systeem wordt uitgespuugd in tabblad Gegevens, en zoals ik het zou willen hebben in het tabblad Resultaat. Met wat uitleg erbij in het document.

Heeft iemand een idee hoe dit aan te pakken met bijvoorbeeld een macro?

 

[roeljongman]

met power query is dit een peuleschilletje.

zie deze site voor wat utileg hoe je op basis van een scheidingsteken data in rijen kan krijgen

Split data in a Cell to Multiple Rows using Power Query - XL n CAD

Data in one or more cells can be sent to multiple rows on the basis of delimiters (Comma, Space, etc.) using Power Query in Excel.

xlncad.com

en daarna doe je nog wat stappen om spaties en evt andere onzichtbare tekens uit het artikelnr te krijgen
en dan is de tabel al klaar

je kunt deze gegevens ook heel makkelijk bijwerken door als er een nieuwe download is deze data in tabblad gegevens te plakken en daarna de query te vernieuwen.

zij bgeoegde bestand voor het resultaat

 

[peter59]

Van het zelfde als @roeljongman maar enkele stapjes minder.

 

[AHulpje]

Of met een simpele macro, Alt+F8, Enter

 

[Oudholland]

Bedankt allemaal voor de snelle reacties!

@roeljongman Het is echt zo makkelijk!?!!
Morgen op de echte data uitproberen, maar het ziet er veelbelovend uit.

Voor de archieven (inclusief mezelf als ik dit later weer nodig heb en het dan even niet meer weet) 2 Youtube-video's die ik net vond waar de Power Query uitgelegd wordt:

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

En als ik dan wat verder kijk wat er met Power Query mogelijk is... Er gaat een wereld voor me open, ik vrees dat ik de komende avonden op YT rondhang om hier meer van te weten te komen. Vooral de maker van de 1e video heeft een hoop duidelijk uitgelegde video's met veel dingen die ik wel kan gebruiken in het werk om dingen makkelijker te doen dan ik tot nu toe kende... O.a. samenvoegen van tabellen en die makkelijk updaten. Bedankt!

@peter59 Wat heb jij anders gedaan dan? Ik zie dat bij jou de gegevens op hetzelfde blad erbij komen, maar welke stappen neem je dan?

@AHulpje Zou je de tekst van de macro hier in een code willen plakken? De beveiliging op de pc hier laat het niet toe om in de gedownloade excel de macro te openen of te bewerken/bekijken.

 

[AHulpje]

Sub Splitsen()
    r2 = 2
    For r1 = 2 To Range("H1").End(xlDown).Row
        artikelen = Split(Cells(r1, 8), "/")
        For a = 0 To UBound(artikelen)
            Sheets("Resultaat").Cells(r2, 1) = Trim(artikelen(a))
            For k = 2 To 8
                Sheets("Resultaat").Cells(r2, k) = Cells(r1, k - 1)
            Next
            r2 = r2 + 1
        Next
    Next
End Sub

 

[Warme bakkertje]

Uitgaande van een scenario van gemiddeld 30 artikels per rij en 100 rijen zitten we al snel aan 24000 schrijfbewegingen naar het werkblad.
Gezien het mogelijk aantal artikels en het mogelijk aantal rijen zou ik alles opbouwen in het geheugen en in 1 keer wegschrijven naar het werkblad.

Sub Splitsen()
    Dim sq(1 To 50000, 1 To 8)
    sn = Blad1.Cells(1).CurrentRegion.Value
    For i = 2 To UBound(sn)
        art = Split(sn(i, 8), "/")
        For j = 0 To UBound(art)
            x = x + 1
            sq(x, 1) = Trim(art(j))
            For jj = 1 To 7
                sq(x, jj + 1) = Application.Index(sn, i, jj)
            Next
        Next
    Next
    Blad2.Cells(2, 1).Resize(x, 8) = sq
End Sub

 

[AHulpje]

@Warme bakkertje
Met 241 rijen (80 kopieën van de 3 rijen uit het voorbeeld), met als resultaat 2800 rijen moet je met mijn methode een hele seconde wachten, met jouw methode duurt het 6 seconden!

 

[Warme bakkertje]

Zo gaat hij beter. De traagste die ik gemeten heb is 0.06 seconden.
Had niet verwacht dat Application.Index de boel zo zou vertragen op grotere bereiken.

Sub SplitsenWB()
    Start = Timer
    Dim sq(1 To 50000, 1 To 8)
    sn = Blad1.Cells(1).CurrentRegion.Value
    For i = 2 To UBound(sn)
        art = Split(sn(i, 8), "/")
        For j = 0 To UBound(art)
            x = x + 1
            sq(x, 1) = Trim(art(j))
            For jj = 1 To 7
                sq(x, jj + 1) = sn(i, jj)
            Next
        Next
    Next
    With Blad2
        .Cells(1).CurrentRegion.Offset(1).ClearContents
        .Cells(2, 1).Resize(x, 8) = sq
    End With
    MsgBox Timer - Start
End Sub

 

[roeljongman]

@roeljongman Het is echt zo makkelijk!?!!
Morgen op de echte data uitproberen, maar het ziet er veelbelovend uit.

Tijd investeren in Powerquery is echt je moeite waard. Ik heb op sommige bestanden echt 80-90% tijdsbesparing gerealiseerd aan knutselen met data.

mijn favoriete youtube kanaal voor Excel is

Leila Gharani

Excel. Copilot. ChatGPT. Power BI. PowerPoint. You use them every day - so why not actually master them? I’ll show you how to: ✔️Automate the stuff that drives you nuts ✔️ Spend less time on grunt work, more time on wow work ✔️ Build reports that look good AND actually make sense What You’ll...

www.youtube.com

Ik heb ook haar cursus van power query gekocht, kost wel iets van 250 euro. maar je hebt oneindig toegang en je krijgt ~185 lessen/ video's voordeel is dat het overgrote deel 4-5 minuten is waar 1 onderwerp wordt besproken dus je hoeft niet door een video van 30-40 minuten te gaan harken om net die ene uitleg van die ene handeling te kunnen vinden.