Kopieeren middels zoekfunctie zonder lege cellen

[RoyH2]

GM,

Ik zit compleet vast met excel, gewoon gebrek aan kennis, niets aan de hand... 

Wat ik graag zou willen is middels een zoekfunctie cellen naar een ander sheet kopieeren, maar dan alleen de cellen met tekst. De lege overslaan dus.

Ik vind het probleem wat lastig uitleggen dus zie bijlage. In sheet2,B3 staat een waarde die in sheet1, C4:H4 gevonden kan worden. Dan moet de bijbehorende gevulde cellen (in het voorbeeld C9, C12, A9, A12) gekopieerd worden naar sheet2.

Dit heb ik geprobeert met formules, maar ik loop erop vast en mijn kennis van VBA is nihil dus dat gaat het helemmaal niet worden.

Kan iemand me opweg helpen?


Gr. Roy

 

[MarcelBeug]

Bekijk bijlage Kopièren middeks zoekfunctie zonder lege cellen.xlsx

Zie werkblad 'Factuur MB'.
Ik heb de formules in H7 en I7 gezet en gekopieerd naar beneden.
Tevens heb ik een printscreen van de factuurdetails opgenomen zodat de resultaten eenvoudig gecontroleerd kunnen worden.

Data extracties zoals deze, leveren redelijk complexe matrixformules op. Deze moeten ingevoerd worden met CTRL-SHIFT-ENTER in plaats van gewoon ENTER. Ze zijn herkenbaar aan de omringende accolades {} die je niet zelf moet invoeren maar die Excel toevoegt als je op CTRL-SHIFT-ENTER drukt.

Mocht je nog vragen hebben (dat zou best eens kunnen ) dan kan ik een en ander nader toelichten.

 

[RoyH2]

Bedankt Marcel, geen idee hoe, maar het werkt fantastisch!!:thumb:

 

[MarcelBeug]

Een belangrijk element van de formules, is de relatieve rij waarin je bent in de resultaatmatrix. In H7 en I7 is dat de 1e rij, in H8 en I8 de 2e enzovoorts.

“De huidige relatieve rij” is de uitkomst van het deel RIJEN(H$7:H7): oftewel 1 op regel 7, 2 op regel 8 (naar beneden gekopieerd) wordt dit deel RIJEN(H$7:H8), enzovoorts.
In H2 wordt eerst gecontroleerd of er nog gegevens zijn voor de huidige rij: dat is het aantal elementen in de betreffende kolom van de detailgegevens. “De betreffende kolom” wordt bepaald met het deel VERGELIJKEN($H$3;FactuurNr;0).
Als het huidige relatieve rijnummer in de resultaatmatrix groter is dan het aantal elementen in de betreffende factuur, dan hoeft niets (“”) ingevuld te worden.
In I2 hoeft dat allemaal niet herhaald te worden: daar wordt gewoon gekeken of H2 leeg is.

Als er nog gegevens zijn voor de huidige relatieve rij, dan moet je uit de detailmatrix (‘Factuur Detail’!$C$9:$F$12) het element ophalen uit “de betreffende rij” en “de betreffende kolom”.
In H2 is “de betreffende kolom” kolom A in de detailsheet, in I2 zoals hierboven uitgelegd.

“De betreffende rij” wordt bepaald met het formuledeel KLEINSTE(ALS(INDEX('Factuur Detail'!$C$9:$F$12;;VERGELIJKEN($H$3;FactuurNr;0)) < > "";RIJ('Factuur Detail'!$C$9:$F$12)-RIJ('Factuur Detail'!$C$9)+1);RIJEN(H$7:H7)):
In het ALS-deel wordt gekeken op welke rijen in de detailgegevens iets staat en hiervan wordt het rijnummer teruggegeven: het deel RIJ('Factuur Detail'!$C$9:$F$12)-RIJ('Factuur Detail'!$C$9)+1 levert de reeks 1; 2; 3; 4 op. (Eventueel zou RIJ($1:$4) hetzelfde opleveren, maar dan ga je “nat” als er regels worden tussengevoegd of verwijderd.)
Bijvoorbeeld bij factuur F0004 krijg je als resultaat uit de ALS-functie: ONWAAR; 5; ONWAAR; 6.

Hiervan wordt met KLEINSTE het juiste geldige element gepakt, afhankelijk van de regel waarop je zit: KLEINSTE({ONWAAR; 2; ONWAAR; 4}; 1) levert 2 op, KLEINSTE({ONWAAR; 2; ONWAAR; 4}; 2) levert 4 op. En dit zijn precies de rijnummers in de detailmatrix die je moet hebben voor factuur F0004.

 

[WHER]

Hier nog een mogelijke aanpak, toegevoegd in het bestand van MarcelBeug.
Bij gebruik van een eenvoudige hulpkolom op het eerste tabblad kan je hetzelfde resultaat bekomen met eenvoudige, niet-matrix formules.