Regels splitsen obv celinhoud

[john171346]

Ik heb een bestand met 2 tabbladen ( 461030 en 461031) ik wil dat dat 2 nieuwe (of aangepaste) tabbladen worden.
In de kolom G heb ik omschrijvingen staan welke per regel gesplitst zijn met "alt-enter"
Ik heb in de omschrijvingen 4 categorieën:
1. dekkend..... beschermd
2. dekkend....onbeschermd
3. blanke lak..... beschermd
4. blanke lak....onbeschermd.

Wat ik nu wil is dat deze 4 categorieën worden opgesplitst in aparte regels. (zie tabblad "461030_gesplitst" daar zijn nu kleuren aangegeven hoe het zou moeten worden. (de originele regels welke verplaatst worden mogen/ moeten uit de regel weg waar het eerst in stond)
Ik weet dat dit lastig is om uit te leggen, maar als er nog vragen zijn, hoor ik dat graag.

John

 

[jverkerk]

Zou denk ik een mooi klusje zijn voor Power query.
Alleen mijn PQ kennis is niet toereikend.
Misschien @jec of peter.

 

[AHulpje]

Probeer het hier eens mee, start de macro Splits met Alt+F8, Enter.

 

[JEC.]

Hier eentje met Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ar = {", onbeschermd", ", beschermd"},
    getLists =  
        Table.AddColumn(Source, "Toelichting", each     
            Text.Split(     
                Text.Combine(   
                    List.Accumulate(Text.Split([toelichting],"#(lf)"),{},(s,c)=>    
                    if s = {} then s & {c} 
                    else if (Text.Contains(List.Last(s),ar{0}) and Text.Contains(c,ar{1})) or (Text.Contains(List.Last(s),ar{1}) and Text.Contains(c,ar{0}))  
                        then s & {"^"} & {c} 
                        else s & {c}
                    ),"#(lf)")
                ,"^")
            ),
    expand = Table.ExpandListColumn(getLists, "Toelichting"),
    delCol = Table.RemoveColumns(expand,{"toelichting"})
in
    delCol

 

[AHulpje]

N.B. Hier en daar ontbreekt de spatie tussen de komma en "beschermd" of "onbeschermd", is dat nog op te vangen met PQ?

 

[john171346]

Ten eerste hartelijk dank voor alle reacties.
Het ziet er al veel belovend uit.
Zowel de oplossing van Ahulpje en Jec zijn erg mooi.
Beide hebben voor en nadelen.
Bij Ahulpje zie ik dat de regels van "beschermd" en "onbeschermd" wel op volgorde moeten staan anders komen er extra regels bij, komt niet zo heel vaak voor dus dat is daarna wel handmatig op te lossen (weer samenvoegen).
En bij Jec zie ik dat deze Power Query niet de "blanke lak" uitsplitst. en hoe start ik deze voor het andere tabblad (ik heb geen kennis van Power query)

Nogmaals alvast bedankt.

John

 

[JEC.]

Ja zeker, ik zag ook nog dat er soms een hoofdletter gebruikt wordt ipv kleine letters. Hier een aangepaste versie

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ar = List.Zip({{", onbeschermd", ", beschermd",", Onbeschermd",", Beschermd","#(lf)"},{",onbeschermd", ",beschermd",",Onbeschermd",",Beschermd","#(lf)#(lf)"}}),
    rep = List.Accumulate(ar,Source,(s,c)=> Table.ReplaceValue(s,c{1},c{0},Replacer.ReplaceText,{"toelichting"})),
    getLists =  
        Table.AddColumn(rep, "Toelichting", each     
            Text.Split(     
                Text.Combine(   
                    List.Accumulate(Text.Split([toelichting],"#(lf)"),{},(s,c)=>    
                    if s = {} then s & {c} 
                    else if (Text.Contains(Text.Lower(List.Last(s)),ar{0}{0}) and Text.Contains(Text.Lower(c),ar{1}{0})) or (Text.Contains(Text.Lower(List.Last(s)),ar{1}{0}) and Text.Contains(Text.Lower(c),ar{0}{0}))  
                        then s & {"^"} & {c} 
                        else s & {c}
                    ),"#(lf)")
                ,"^")
            ),
    expand = Table.ExpandListColumn(getLists, "Toelichting"),
    delCol = Table.RemoveColumns(expand,{"toelichting"})
in
    delCol

 

[JEC.]

De blanke lak had ik nog niet bekeken inderdaad, morgen kan ik misschien weer even kijken. Nu eerst de kroeg in voetbal kijken

PS: je kunt dezelfde code gebruiken voor je andere tabblad maar dan alleen de Source regel aanpassen zodat deze verwijst naar de andere tabel. Nu wordt verwezen naar Table1

 

[peter59]

Ik heb ook een poging ondernomen.
Iets over een andere boeg gegooid.
D.m.v. power query het e.e.a. zo goed als mogelijk opgeschoond en hier een draaitabel van gemaakt.
Het verdiend absoluut niet de schoonheidsprijs maar de aangeleverde basis is ook niet om over naar huis te schrijven. M.a.w. is het geheel mijn inziens nooit en te nimmer waterdicht te krijgen. Maar ja.
Kijk maar eens of dit een beetje in de richting komt.

 

[snb]

Als je Excel als een database wil gebruiken dien je gegevens ook in de vorm van een database (genormaliseerde tabel) te struktureren.
Zie de bijlage.
Daarna doet de draaitabel (zie bijlage) al het werk voor je.

 

[john171346]

Weer bedankt voor alle "updates".
Het ziet er al goed uit.
De versie van SNB kan ik niet heel veel mee, terwijl die er wel goed uitzie.
Dit excel bestand is een export uit een ander softwarepakket, en via deze manier kan ik deze info ook weer importeren, maar een draaitabel kan ik niet importeren.
Ik ga nog even kijken welke versie (van wie) ik het beste kan gebruiken.
In ieder geval heel hartelijk bedankt allemaal.

John

 

[peter59]

Die draaitabel is geen import maar een vervolg van datgene wat jij hebt geïmporteerd voor te splitsen.