• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Kopieren zonder witregels met formule

Status
Niet open voor verdere reacties.
JEC.

JEC.

Terugkerende gebruiker
Lid geworden
27 feb 2019
Berichten
4.330
Office versie
365
Goedemiddag,

Kan iemand mij helpen aan een formule:

Als in tabblad 1 een X staat in Kolom M
Dan de tekst descriptions (Kolom H) kopieren naar tabblad 2 onder punch descriptions (kolom B)

Maar:
Er mogen geen witregels tussen de descriptions in staan.

Alvast bedankt
Jim
 

Bijlagen

  • issuelijst opzetje.xlsx
    16,9 KB · Weergaven: 38
Invoeren met Enter.
Code: 
=ALS.FOUT(INDEX(Sheet1!H$7:H$100;SOMPRODUCT(GROOTSTE((Sheet1!M$7:M$100="x")*(Sheet1!H$7:H$100>0)*(RIJ(H$1:H$94));SOMPRODUCT((Sheet1!M$7:M$100="x")*(Sheet1!H$7:H$100>0))-RIJ()+RIJ($B$7))));"")
 
Die kan wat simpeler denk ik Harry...
Code: 
=ALS.FOUT(INDEX(Sheet1!$H:$H;KLEINSTE(ALS(Sheet1!$M$7:$M$100="x";1;9^99)*ALS(Sheet1!$H$7:$H$100<>"";1;9^99)*RIJ($7:$100);RIJ(A1)));"")
Matrixfunctie (dus afsluiten met Control+Shift+Enter)
 
Uiteraard weet ik dat, ik heb er al duizenden gemaakt op die manier. ;)

Eens een keer wat anders dan Ctrl+Shift+Enter.
9^99 kan je ook wel weglaten.
 
Laatst bewerkt:
;)

Als er 1 ALS-functie zou staan kan de 9^99 weg inderdaad. Bij twee ALS-functies niet lijkt me... ONWAAR * ONWAAR = 0. De KLEINSTE vindt dan altijd deze 0.
 
Duizenden schreef ik toch? ;)
Code: 
=ALS.FOUT(INDEX(Sheet1!$H$7:$H$100;KLEINSTE(ALS((Sheet1!$M$7:$M$100="x")*(Sheet1!$H$7:$H$100>0);RIJ($1:$94));RIJ(A1)));"")
 
Bedankt mannen! Ga hem morgen proberen te kraken!
 
Goedemorgen

De formule werkt in het voorbeeldbestand wel.

In het echte bestand wil het echter niet lukken.
Ik plaats het volledige bestand dan maar.

De vraag staat op sheet 2\

Jim
 

Bijlagen

  • voorbeeld2.xlsx
    545,6 KB · Weergaven: 33
Code: 
=ALS.FOUT(INDEX(Sheet1!$M$2:$M$9000;KLEINSTE(ALS((Sheet1!$Q$2:$Q$9000="x")*(Sheet1!$M$2:$M$9000>0)*(Sheet1!$A$2:$A$9000=$C$3);[COLOR=#ff0000]RIJ($1:$8999));RIJ(A1)[/COLOR]));"")
Inbrengen met Ctrl+Shift+Enter.
 
Spot de verschillen:
Code: 
=ALS.FOUT(INDEX(Sheet1!$M:$M;KLEINSTE(ALS((Sheet1!$Q$2:$Q$9000="x")*(Sheet1!$M$2:$M$9000>0)*(Sheet1!$A$2:$A$9000=$C$3);RIJ($2:$9000));RIJ(A1)));"")

@HSV: Mea Culpa.
 
Laatst bewerkt:
@AlexCel, maakt niet uit.

De formules zijn al zwaar genoeg, maar hiervan ..
Code: 
=ALS.FOUT(INDEX(Sheet1![COLOR=#ff0000]$M:$M[/COLOR]
...worden ze nog trager hoor.

Onderstaand stukje kan er bij ons beiden ook wel uit denk ik zo.
Code: 
*(Sheet1!$M$2:$M$9000>0)
 
Laatst bewerkt:
Dit werkt idd wanneer ik letterlijk de cel met projectnummer kopieer en plak(dus niet wanneer ik hetzelf intyp). Ook wanneer ik een gedeelte van een projectnummer plak, pakt hij die niet.
Dus als ik projectnummer 35P182675 wil opzoeken zou dit ook al moeten lukken als ik 182675 in zou typen


Werkt deze formule niet wanneer je verwijst naar een powerquery tabel?
 
Laatst bewerkt:
@HSV.
1. Fundamenteel vraagje dan: kun je mij uitleggen waarom? Er gebeurt m.i. niets met de hele kolom, er wordt alleen naar een rij in de index verwezen.
2. Inderdaad: dat stukje zal er bij ons beide in vanwege het geplaatste voorbeeld... dus kan eruit hier. Weer wat kortere (snellere) formule.

@Jim
Nog even voortbordurend op eerdere formule:
Code: 
=ALS.FOUT(INDEX(Sheet1!$M:$M;KLEINSTE(ALS((Sheet1!$Q$2:$Q$9000="x")*ISGETAL(VIND.SPEC("*"&$C$3&"*";Sheet1!$A$2:$A$9000));RIJ($2:$9000));RIJ(A1)));"")
Voldoet dit? Nu kun je ook zoektermen invoeren. Het e.e.a. wordt wel wat trager hierdoor (ook als je bereik aanpast).
 
Laatst bewerkt:
Helemaal top!
Ik zou graag weten hoe de formule precies werkt xD

Bedankt voor de moeite, toch niet zo gemakkelijk als ik dacht.
 
Minder rekenkracht met onderstaande in mijn versie.
Code: 
=ALS.FOUT(INDEX(Sheet1!M$2:M$9000;KLEINSTE(ALS((ISGETAL(VIND.SPEC(C$3;Sheet1!A$2:A$9000))*(Sheet1!Q$2:Q$9000="x"));RIJ($1:$8999));RIJ(A1)));"")
 
Kan iemand mij vertellen wat KLEINSTE() doet in deze formule en hoe de witregels worden weggehaald?
 
Een korte toelichting:
1. Het betreft een matrix-functie, dus de uitkomst van de formule is niet één uitkomst, maar meerdere.
2. Alles binnen de ALS-functie checkt of de regel voldoet aan de voorwaarden (x in kolom Q, gedeelte van de tekst uit C3 in kolom A).
3. Zo ja wordt het RIJ-nummer in de matrix gezet, zo niet de waarde ONWAAR
4. KLEINSTE heeft 2 gedeelten, de matrix met RIJ-nummers/ONWAAR, en het gedeelte RIJ(A1)(=1). Bij doortrekken wordt deze laatste RIJ(A2)(=2), rij(A3)(=3)
5. Oftewel: de KLEINSTE-functie zoekt bij doortrekken in de kolom in de eerste regel de kleinste (duh) RIJ-waarde (ONWAAR wordt genegeerd), de tweede regel de op-1-na-kleinste waarde, de derde regel de op-2-na-kleinste waarde etc.
6. Met deze waarden wordt via de INDEX de bijbehorende waarde uit kolom M opgezocht.
7. Witregels staan dus als ONWAAR in de matrix en worden genegeerd bij het opzoeken van kleinste (rij-)waarden.
8. Als er geen op-X-na-kleinste waarden meer zijn geeft de INDEX een foutmelding, die door de ALS.FOUT wordt omgezet in "".

Lastig kort op te schrijven, naar hopelijk verduidelijkt dit het e.e.a.
 
Bedankt voor de uitleg!

Ik begin het beter te begrijpen.

Gr

Jim
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan