Kolommen van naast elkaar, onder elkaar zonder lege velden

[hvdb1706]

Kan iemand me helpen bij het volgende.

Ik heb op een werkblad 5 kolommen met gegevens - hiervan zijn niet alle cellen ingevuld. Die gegevens zouden in een ander werkblad allemaal ONDER ELKAAR geplaatst moeten worden (in één kolom) ZONDER de lege cellen.

Dit is een gedeeld document en mijn ervaring leert dat de sheet dan flipt bij gebruik van VBA - dus ik zoek een oplossing zonder VBA.

 

[Excelbat]

Plaats even een voorbeeldbestand.

Greetz/Excelbat

 

[hvdb1706]

Zie bijlage,

Klein voorbeeld want echte bestand bestaat uit heel wat meer lijnen (wel ook 5 kolommen).

Snap je wat ik bedoel?

 

[HSV]

Matrixformules.

=ALS.FOUT(INDEX(B$2:F$5;REST(KLEINSTE(ALS(B$2:F$5>0;(B$2:F$5>0)*KOLOM(A2:E5)*100+RIJ($1:$4));RIJ(A1));100);GEHEEL(KLEINSTE(ALS(B$2:F$5>0;(B$2:F$5>0)*KOLOM(A2:E5)*100+RIJ($1:$4));RIJ(A1))%; ));"")

Of zonder dubbelingen.

=ALS.FOUT(INDIRECT(TEKST(MIN(ALS(ISLEEG(B$2:F$5)+AANTAL.ALS(B$9:B9;B$2:F$5);"";RIJ($2:$5)*1000+KOLOM($B$1:$F$1)));"R0\"&ALS(CEL("type";XFD1)="g";"K???";"S???"));0);"")

Info; In de laatste formule staat: "S???"
Verander dit als je een Engelstalige versie hebt in: "C???"

 

[hvdb1706]

Super bedankt!

Dit werkt inderdaad op mijn voorbeeldbestandje, maar als ik het probeer om te vormen voor mijn bestand werkt het precies niet?

In bijlage heb ik mijn voorbeeldbestand aangepast aan de rijen en kolommen zoals het moet zijn.

De info moet uit werkblad "ARCHIEF" gehaald worden en in werkblad "STATS" terecht komen.

Dubbele waarden mogen erin staan dus de eerste formule is ok.

Het zou echt geweldig zijn moest je me hierbij kunnen helpen! (ik ben een leek )

 

[HSV]

Zonder de "x":

=ALS.FOUT(INDEX(ARCHIEF!O$5:S$12;REST(KLEINSTE(ALS(ARCHIEF!O$5:S$12>0;(ARCHIEF!O$5:S$12>0)*KOLOM(A:E)*100+RIJ($1:$8));RIJ(A1));100);GEHEEL(KLEINSTE(ALS(ARCHIEF!O$5:S$12>0;(ARCHIEF!O$5:S$12>0)*KOLOM(A:E)*100+RIJ($1:$8));RIJ(A1))%; ));"")

Zoals het in je bestand staat incl. de "x".

=ALS.FOUT(INDEX(ARCHIEF!O$5:S$12;REST(KLEINSTE(ALS(ARCHIEF!O$5:S$12<>"x";(ARCHIEF!O$5:S$12<>"x")*KOLOM(A:E)*100+RIJ($1:$8));RIJ(A1));100);GEHEEL(KLEINSTE(ALS(ARCHIEF!O$5:S$12<>"x";(ARCHIEF!O$5:S$12<>"x")*KOLOM(A:E)*100+RIJ($1:$8));RIJ(A1))%; ));"")

 

[hvdb1706]

Bedankt! Dit werkt!

Maar...

Als ik de formule doortrek naar beneden krijg ik bij:

=ALS.FOUT(INDEX(ARCHIEF!O$5:S$217;REST(KLEINSTE(ALS(ARCHIEF!O$5:S$217>0;(ARCHIEF!O$5:S$217>0)*KOLOM(A:E)*100+RIJ($1:$213));RIJ(A99));100);GEHEEL(KLEINSTE(ALS(ARCHIEF!O$5:S$217>0;(ARCHIEF!O$5:S$217>0)*KOLOM(A:E)*100+RIJ($1:$213));RIJ(A99))%; ));"")

Als resultaat : #OVERLOOP!

Het bereik in mijn bestand is O5:S217

 

[Willempie2016]

Dealerlijst

Hierbij een oplossing zonder VBA. Ik heb de formules doorgetrokken tot rij 250 en enkele kolommen verborgen voor het gezicht.
Volgens mij is dit de bedoeling; Laat het even weten.

 

[HSV]

Formule voor N2 en verder naar onderen kan iets korter en gemakkelijker.

=ALS(M2="";"";INDEX(B$2:F$230;SOMPRODUCT((H$2:L$230=M2)*RIJ(A$1:A$229));SOMPRODUCT((H$2:L$230=M2)*KOLOM(A:E))))

Verder een mooie oplossing.

 

[Willempie2016]

Index

Dag Harry,
Ik zat al te stoeien met de index oplossing maar duurde me te lang.
De mogelijkheid met somproduct kende ik zo niet dus.....ook blij.
Zou jij ook eens willen kijken naar mijn vraag over het afsluiten van geopende bestanden?
Groet, Wim

 

[hvdb1706]

Super!

Maar mijn bereik is O5:S224 voor de dealers en T5:X224 voor de hulpkolommen.

Als ik de formule knip en plak werkt die niet meer?

 

[Willempie2016]

Vraagteken ?????

1) Het is handig om meteen zoveel mogelijk gegevens te verstrekken zoals dus ook de plaats waar e.e.a. moet komen te staan. Dat voorkomt extra werk.
2) In formules zitten soms verwijzingen die bij een andere plaatsing niet meer werken.
3) Als je het meteen werkend wil hebben moet je het voorbeeldbestand beter maken
4) Als je de formules begrijpt (of leert begrijpen) kan je het zelf aanpassen; zo is deze website ook bedoeld.
5) Met welk systeem wil je verder? Er zijn je nu 2 oplossingen geboden.

 

[Willempie2016]

Pfffff

Als je per (1 of meerdere) kolom(men) de rijen 1 t/m 250 selecteert en je knipt en vervolgens selecteer je de cel op de plaats waar je het wil hebben en je plakt dan werkt het perfect.
Zie aangepast bestand.

 

[Willempie2016]

Als de verborgen kolommen je in de weg zitten moet je die dus op dezelfde manier verplaatsen; maakt niet uit waar naar toe.