volgenden zoeken in een matrix

[Plotinus]

L.S.

Ik heb een bestand met daarin o.a diverse bedrijven in een kolom en werknemers van die bedrijven in een andere kolom (zie voorbeeld; blad 1). Op blad 2 wil ik in C2 de bedrijven uit een menu selecteren waarna daaronder alle medewerkers van het bedrijf moeten worden getoond. Met verticaal zoeken is dit niet op te lossen, want dan krijg je enkel de eerste die wordt gevonden. Met Index en Vergelijken heb ik ook geprobeerd, maar daar loop ik ook mee vast. Het moet volgens mij iets zijn met 'Kleinste' ed, maar dat lukt me niet.

Op blad 2 heb ik bedrijf A geselecteerd en daaronder handmatig aangegeven welke werknemers daar bij horen. In E4 heb ik het resultaat met mijn eigen formule weergegeven, maar dat lukt dus alleen voor de eerste werknemer.

Iemand een idee hoe ik de andere werknemers van een bedrijf kan ophalen via de selectie in cel C2 en netjes onder elkaar kan weergeven[?

 

[HSV]

In C4 en doorvoeren naar onderen.

=ALS(RIJ()-RIJ($C$4)>=AANTAL.ALS(Blad1!$E$2:$E$100;$C$2);"";INDEX(Blad1!$A$2:$A$100;SOMPRODUCT(GROOTSTE((Blad1!$E$2:$E$100=$C$2)*(Blad1!$A$2:$A$100>0)*(RIJ($A$2:$A$100)-RIJ($C$2)+1);AANTAL.ALS(Blad1!$E$2:$E$100;$C$2)-RIJ()+RIJ($C$4)))))

 

[rebmog]

Of met deze matrixformule (ingeven met Ctrl+Shift+Enter):

=ALS.FOUT(KLEINSTE(ALS(Blad1!E$2:E$100=C$2;Blad1!A$2:A$100);RIJ(A1));"")

 

[popipipo]

Ook ideaal op te lossen met een draaitabel

 

[Plotinus]

Dank voor de hulp; beide methoden werken, alleen de methode van redmog vind ik door zijn compactheid eleganter en heb ik dus toegepast; sorry HSV Ik had overigens inmiddels zelf ook al een matrix-oplossing gevonden, maar iets omslachtiger dan van redmog.
Ik vraag me overigens wel af of het ook zou kunnen zonder gebruik te maken van getallen, maar dus alleen op naam. Ik heb in mijn project iedereen een code meegegeven omdat ik had bedacht dat het zo moest kunnen, maar kan het ook zonder?

Het klopt natuurlijk dat het ook via een draaitabel kan, maar is hier niet wenselijk, vandaar deze gezochte oplossing.

 

[HSV]

Stop je vraag in een voorbeeldje en iedereen kan zien wat je bedoeld.

 

[rebmog]

Als kolom A van blad1 tekst bevat i.p.v. getallen dan werkt de formule van HSV ook goed. Hierbij een aangepaste matrixformule die dan ook goed werkt:

=ALS.FOUT(INDEX(Blad1!A$2:A$100;KLEINSTE(ALS(Blad1!E$2:E$100=C$2;RIJ(A$2:A$100)-1);RIJ(A1)));"")

 

[Plotinus]

Prachtig, het werkt; mijn dank is groot! Wel nog een klein foutje uit je formule gehaald redmog (denk ik): RIJ(A$2:A$100) moet RIJ(Blad!A$2:A$100) zijn.

Ik heb nog één wens in dit verband en hiervoor heb ik het betreffende voorbeeldje iets aangepast. Ik zou graag in de tabel die wordt gevuld op Blad2 na selectie van het bedrijf (cel C2), de niet gevulde rijen automatisch willen verbergen, dus de rijen waar ik '-' noteer. Volgens mij is dit een fluitje van een cent in VBA, maar ik heb al in geen 10 jaar met VBA gewerkt en weet gewoon niet meer hoe hier aan te beginnen. Ik denk aan een For-loop die alle rijen van de tabel evalueert en verbergt als ze '-' bevatten. En omgekeerd: na nieuwe selectie van een bedrijf moeten de rijen die eerst wellicht verborgen waren en nu inhoud bevatten worden getoond.

Overigens jammer dat het lettertype en lettergrootte niet is te wijzigen in lijst van gegevensvalidatie (cel C2). Ik heb dit ook nog uitgetest met formulierbesturingselementen en ActiveX-besturingselementen, maar loop tegen hetzelfde probleem aan. WE zullen hier maar vreede mee hebben.

 

[popipipo]

Ik zou graag in de tabel die wordt gevuld op Blad2 na selectie van het bedrijf (cel C2), de niet gevulde rijen automatisch willen verbergen, dus de rijen waar ik '-' noteer.

Misschien dan toch maar een draaitabel

 

[rebmog]

Prachtig, het werkt; mijn dank is groot! Wel nog een klein foutje uit je formule gehaald redmog (denk ik): RIJ(A$2:A$100) moet RIJ(Blad!A$2:A$100) zijn.

Is geen foutje. De functie RIJ wordt alleen maar gebruikt voor het genereren van het juiste index-nummer. Een verwijzing naar een ander blad is hierbij niet van belang. Je zou daarom RIJ(A$2:A$100)-1 ook kunnen vervangen door RIJ(A$1:A$99).

 

[Plotinus]

Bovenstaande wensen inmiddels gerealiseerd middels VBA