Zoekende naar oplossing om een hiërarchie aan te brengen over kolommen vs regels

[ManuelMagallon]

Goedemorgen,

Ik ben zoekende naar een oplossing om handwerk te automatiseren. Om excel bestanden in bepaalde tools in te lezen, moeten deze voldoen aan een formaat.
De input bestanden voldoen echter niet aan dit formaat. Normaliter kan dit redelijk snel met de hand worden gedaan. Maar de spreadsheets beginnen te groeien...
Er ontstaat dus een behoefte naar automatiseren in de vorm van een stuk VBA code, een macro of (onwaarschijnlijk) een complexe formule.

Ik zal pogen het zo goed mogelijk uit te leggen.

Het input bestand bevat regels. Een regel heeft 4 kolommen. Iedere kolom is een niveau.
Functioneel is het een hiërarchische lijst waar waarden in de eerste kolom, een parent is voor de overige kolommen, etc.
Bijv, een gebouw is niveau 1. het dak hoort bij het gebouw en is niveau 2. Het dak bestaat uit dakpannen dit is dan niveau 3.

NU staat dit op 1 regel dus:

NIVEAU 1 NIVEAU 2 NIVEAU 3
Gebouw Dak Dakpannen

Hieruit is visueel niet goed op te maken dat het gaat om een hiërarchie.

Wat ik zou willen is het volgende:
NIVEAU 1 NIVEAU 2 NIVEAU 3
Gebouw
Gebouw DAK
Gebouw DAK Dakpannen

Ik hoop dat mijn vraag op deze manier een beetje duidelijker wordt. Ik heb ook een voorbeeld excel toegevoegd bij dit bericht.
Als er vragen zijn, verneem ik die graag!

En alvast enorm bedankt voor het mee denken!

Hartelijke groet,

Manuel Magallon

Bekijk bijlage HelpMij.xlsx

 

[snb]

Als het gaat om overzichtelijkheid zou mijn voorkeur uitgaan naar werkblad 'snb' in de bijlage.

Sub M_snb()
  sn = Sheet1.Cells(9, 3).CurrentRegion
  sp = Array(sn(2, 1), sn(2, 2), sn(2, 3))
   
  For j = 3 To UBound(sn)
     For jj = 1 To 3
      If sn(j, jj) = sp(jj - 1) Then
        sn(j, jj) = ""
      Else
        sp(jj - 1) = sn(j, jj)
      End If
     Next
  Next
   
  Sheet1.Cells(9, 3).CurrentRegion.Offset(30) = sn
End Sub

 

[ManuelMagallon]

Hoi Snb,

Ten eerste bedankt voor je antwoord.
Kun je ook iets voor elkaar krijgen wat volgens mijn vraag voorkeur heeft?
Het is naast het visuele aspect ook nodig voor het inlees formaat in andere systemen.
Het herhalen van woorden is niet noodzakelijk, echter de positie wel.

Gr Manuel

 

[snb]

Je kunt zelf de code eenvoudig aanpassen.

 

[ManuelMagallon]

Ik ben helaas niet goed bekend met VBA.
Zou je me op weg kunnen helpen, door aan te geven wat wat is?
Ik kan deze code (nog) niet lezen.

Bvd

Manuel

 

[sylvester-ponte]

Manuel,
zitten er ook wel eens lege regels in je bestand?

en op welke cel begint het originele bestand? of wisselt dat wel eens?

mag het originele bestand overschreven worden? of moet het op een nieuw blad geplaatst worden?

 

[sylvester-ponte]

hier een eenvoudig voorbeeldje :

 

[MarcelBeug]

Oplossing met formules

Bekijk bijlage HelpMij MB.xlsx

Bijgaand een oplossing met formules. Zie werkblad GEWENSTE OUTPUT MB.

Ik heb voor de input (Input!C10:F28) de naam Input gedefinieerd (zie tab "Formules", sectie "Gedefinieerde namen").

Werkblad GEWENSTE OUTPUT MB:
In B10, gekopieerd naar beneden:

=ALS(RIJ(B10)<=3+RIJ(B$10);1;ALS(F9<>"";B9+1;B9))

De inhoud van kolom B is onzichtbaar doordat ik aangepast data format ;;; heb gebruikt.
In C10, gekopieerd naar beneden:

=INDEX(Input;$B10;1)

In D10, gekopieerd naar rechts t/m F10 en vervolgens naar beneden:

=ALS(OF(C10<>C9;C10="";RIJEN(D$10:D10)<KOLOMMEN($C10:D10));"";INDEX(Input;$B10;KOLOMMEN($D10:E10)))

De 3e voorwaarde bij OF zorgt voor de lege cellen op regel 10 voor Niveau 2, regels 10 en 11 voor Niveau 3 en regels 10 t/m 12 voor Niveau 4.

Cellen zijn gekleurd met behulp van voorwaardelijke opmaak met formule:

=EN(C10<>C9;C10<>"")

van toepassing op =$C$10:$E$48

Het resultaat is hetzelfde als het origineel, maar dan zonder de vergissing bij de overgang naar "Oosterschelde" in niveau 1.

Bij verversing van de input hoef je alleen maar:
1. De gedefinieerde naam Input bij te werken met de nieuwe input range.
2. In het werkblad met de output: de formules in de kolommen B t/m E verder naar beneden doortrekken (zover als nodig) c.q. overbodige regels verwijderen. Niet kolom B vergeten, want die is onzichtbaaar!

 

[popipipo]

Een alternatief, dat naar mijn mening ook alles verduidelijkt, is een draaitabel.

 

[ManuelMagallon]

Goedemorgen,

Bedankt allen voor de reacties! Geweldig.

@Sylvester-ponte: je oplossing is echt gaaf! Er zullen geen wit regels in zitten. Dit is een voorwaarde. Daarentegen zullen de bestanden normaliter in cel A2 gaan beginnen. Het aantal kolommen wisselt ook wel eens. Soms zijn het 2 niveau en soms zijn het er 7. Is in die zin jouw methode goed aan te passen? Of is het mogelijk deze dynamisch te houden?

@Marcelbeug: bedankt voor je hulp! Ik zal de formules eens bekijken. Wat zou jij zeggen, formule gebruiken of vba?

@Popipipo: bedankt voor je antwoord. Een draaitabel is echter niet het gewenste resultaat. Wellicht voor het visuele wel, maar niet om mee verder te werken.

 

[MarcelBeug]

@Marcelbeug: bedankt voor je hulp! Ik zal de formules eens bekijken. Wat zou jij zeggen, formule gebruiken of vba?

Graag gedaan. Tja, ik doe zelf weinig met VBA, dus dat maakt mij al gauw voorstander van formules. In dit geval zijn de formules niet al te ingewikkeld en met de bijgeleverde toelichting lijkt mij dit een prima te onderhouden oplossing.
Wellicht dat een fervent VBA-er hier anders over denkt?

 

[sylvester-ponte]

je schrijft

Daarentegen zullen de bestanden normaliter in cel A2 gaan beginnen.

dat is niet altijd.
is er een manier waarop de macro kan weten waar de tabel begint?
bvb: linksboven staat altijd Niveau1
of zoek naar de eerst regel en begin daar onder ( ivm met de kopteksten van de tabel )
laat eens zien hoe een tabel er in werkelijkheid uitziet.
het is ook mogelijk de macro te laten vragen waar de tabel begint en hoeveel kolommen hij bevat.

zitten er soms ook lege kolommen in?

 

[ManuelMagallon]

Dat zou de beste oplossing zijn: de macro laten vragen. Lijkt mij het meeste flexibel.

Edit:
Het kan heel variabel zijn. Mogelijk wordt in de eerste kolom heel andere informatie getoond.
Wat ik altijd doe, ik maak een kopie van het originele bestand en verwijder daarin de kolommen die ik niet nodig heb.

Daardoor begint Niveau 1 altijd linksboven.