Excel verwijzing naar eerste b.l. waarde in kolom ervoor

[Bobbje]

Mijn vraag
Goedemorgen allen!

Ik heb een soort van boomstructuur gemaakt in Excel en nu moet ik daarbij de bovenliggende waarde herhalen.
De is altijd de eerst waarde in de kolom ervoor. Weet iemand hoe dat makkelijk en automatisch kan?
Zie de bijgevoegde afbeelding.
Het gaat om een goede 1200 regels kolommen, dus handmatig wordt best wel een werkje.



Relevante software en hardware die ik gebruik
Excel

Wat ik al gevonden of geprobeerd heb
Een ALS formule maar die wordt veelste lang

[SinergyX]

Je zou hem in 2 stappen kunnen, daarna de formule weer samenvoegen tot 1.

Eerst stel je vast welke van de 4 kolommen gevuld is (hor.zoeken werkt nog steeds?) en vervolgens een als isleeg <waarde 1 cel 1links/1omhoog>, dan waarde, anders waarde formule erboven.

Geneste versie zou zo lang worden als je totaal kolommen zou hebben, wat dan wel de makkelijkste manier is.

[F_J_K]

Je wilt dus kolom E vullen? Eerste wat in me opkomt, E5: als B5 leeg dan =E4, anders =B5. Herhalen voor hele kolom.

Als je iets anders bedoelt: misschien kan je inspiratie opdoen in iets als zoeken bij google naar 'excel formula find next filled cell' -> https://www.mrexcel.com/b...-blank-cell-value.951888/

[Bobbje]

F_J_K schreef op donderdag 1 april 2021 @ 08:51:
Je wilt dus kolom E vullen? Eerste wat in me opkomt, E5: als B5 leeg dan =E4, anders =B5. Herhalen voor hele kolom.

Als je iets anders bedoelt: misschien kan je inspiratie opdoen in iets als zoeken bij google naar 'excel formula find next filled cell' -> https://www.mrexcel.com/b...-blank-cell-value.951888/

Ja wil kolom E vullen, maar het loopt door tot kolom H en rij 1200. Als ik via jou manier ALS formules ga maken kom ik nooit van G421 naar E344 waar mischien een bovenliggende zit.

De link die je meestuurt werkt wel, alleen dan krijg ik de bovenste waarde en niet de onderste.
Dan zegt kolom E bij B3.2 -> A1 i.p.v. B3....

[KabouterSuper]

Met wat hulpkolommen is het goed te doen, ook voor 1200 regels en 10 kolommen

Let op, onderstaande formules gelden als je een header hebt en dus in rij 2 begint! In jouw voorbeeld heb je geen header.

Recept:
Voor elk level in je boomstructuur maak je een extra kolom. In deze kolommen zet je de volgende formules:

code:

J2:=IFERROR(MATCH("ZZZZZZZZZZ",A$1:A1,1),0)
K2:=IFERROR(MATCH("ZZZZZZZZZZ",B$1:B1,1),0)
L2:=IFERROR(MATCH("ZZZZZZZZZZ",C$1:C1,1),0)
..
J3:=IFERROR(MATCH("ZZZZZZZZZZ",A$1:A2,1),0)
K3:=IFERROR(MATCH("ZZZZZZZZZZ",B$1:B2,1),0)
L3:=IFERROR(MATCH("ZZZZZZZZZZ",C$1:C2,1),0)
...

De formules zijn dus allemaal hetzelfde als je copy-paste. Je vindt hiermee per kolom de laatste waarde boven de rij van de cel. Dat is overigens de reden om de data op rij 2 te beginnen. Je krijgt anders een foute formule aangezien A$1:A0 niet mag.

Daarna volgt je antwoord uit:

code:

I2: =OFFSET($A$1,MAX(J2:L2)-1,MATCH(MAX(J2:L2),J2:L2,0)-1)
I3: =OFFSET($A$1,MAX(J3:L3)-1,MATCH(MAX(J3:L3),J3:L3,0)-1)
I4: =OFFSET($A$1,MAX(J4:L4)-1,MATCH(MAX(J4:L4),J4:L4,0)-1)
...

MAX(J2:L2) is de rij waarin de bovenliggende waarde staat, MATCH(MAX(J2:L2),J2:L2,0) is de bijbehorende kolom.

[ Voor 11% gewijzigd door KabouterSuper op 01-04-2021 11:55 ]

[SinergyX]

=ALS(ISLEEG(A4);ALS(ISLEEG(VERSCHUIVING(A4;-1;VERGELIJKEN(TEKST.SAMENVOEGEN(A4;B4;C4;D4;E4;);A4:E4)-2));I3;VERSCHUIVING(A4;-1;VERGELIJKEN(TEKST.SAMENVOEGEN(A4;B4;C4;D4;E4;);A4:E4)-2));A4)

Verdient geen hoofdprijs, maar werkt wel

Edit, niet helemaal, zie dat je ook top gebruikte

Al zou ik gewoon met tussenkolomen blijven werken, dat gaat aanzienlijk makkelijker om stap voor stap data te normaliseren dan het in 1x te willen doen.

[ Voor 31% gewijzigd door SinergyX op 01-04-2021 11:24 ]

[g0tanks]

Volgens mij werkt onderstaande, maar alleen als de echte data het format hanteert zoals in jouw voorbeeld. Ik bepaal eerst met INDEX+MATCH de kolomnummer (1-4) waarin een waarde staat. Vervolgens pas ik de volgende logica toe om de bovenligende waarde te bepalen:

1. TOP
2. TOP
3. Eerste karakter van ingevulde waarde (A1 wordt A)
4. Eerste twee karakters van ingevulde waarde (A1.1 wordt A1)

code:

=CHOOSE(MATCH(TRUE;INDEX(A2:D2<>0;);0);"Top";"Top";LEFT(C2;1);LEFT(D2;2))

[ Voor 5% gewijzigd door g0tanks op 01-04-2021 12:32 ]