Lijst van cellen die bepaalde string bevatten

[Sanglan]

Hallo,

Ik heb een excelbestand met een hele lange lijst aan (Chinese) termen, uiteindelijk moeten het er zo'n 25.000 stuks worden.
Ik wil graag in een ander tabblad een overzicht maken van alle termen die een bepaald karakter bevatten. Zie het voorbeeld:

Bekijk bijlage voorbeeld.xlsx

Om maar even een Nederlands voorbeeld te gebruiken:

Stel er staan in het tabblad 'lijst':
a
aba
ba
xe
zj

Ik wil dan graag een Index/match maken op tabblad 'voorblad' die alles waar een 'a' (of andere string) in voorkomt onder elkaar zet. In dit geval:
a
aba
ba

Ik heb al tientallen forums afgestruind, maar ik krijg het niet voor elkaar.

Bedankt voor de hulp!

 

[SjonR]

Probeer deze eens:

=ALS.FOUT(INDEX(Lijst!$A$1:$A$16893;KLEINSTE(ALS(INTERVAL(ALS(ISFOUT(VIND.SPEC(TRANSPONEREN($B$4);Lijst!$A$1:$A$16893));"";RIJ(Lijst!$A$1:$A$16893)-MIN(RIJ(Lijst!$A$1:$A$16893))+1);RIJ(Lijst!$A$1:$A$16893)-MIN(RIJ(Lijst!$A$1:$A$16893))+1)=RIJEN($B$4);RIJ(Lijst!$A$1:$A$16893);"");RIJ(A1)));"")

invoeren met CTRL-SHIFT + ENTER en dan een heel eind naar beneden doortrekken.

 

[HSV]

=als.fout(index(a$1:a$100000;kleinste(als(isgetal(vind.spec(d$1;a$1:a$100000));rij(a$1:a$100000));rij(a1)));"")

 

[edmoor]

Of, als je het leuk vind, een stukje VBA. Dan hoef je ook niks door te trekken:
Bekijk bijlage Kopie van voorbeeld.xlsm

 

[AlexCEL]

B7, tabblad zoekfunctie:

=ALS.FOUT(INDEX(Lijst!$A:$A;KLEINSTE(ALS(ISGETAL(VIND.SPEC($B$4;Lijst!$A$1:$A$16893));RIJ($1:$16893));RIJ(A1)));"")

Afsluiten met Control+Shift+Enter na invoeren (matrixfunctie) en daarna doorvoeren naar onderen.

 

[Sanglan]

Probeer deze eens:

=ALS.FOUT(INDEX(Lijst!$A$1:$A$16893;KLEINSTE(ALS(INTERVAL(ALS(ISFOUT(VIND.SPEC(TRANSPONEREN($B$4);Lijst!$A$1:$A$16893));"";RIJ(Lijst!$A$1:$A$16893)-MIN(RIJ(Lijst!$A$1:$A$16893))+1);RIJ(Lijst!$A$1:$A$16893)-MIN(RIJ(Lijst!$A$1:$A$16893))+1)=RIJEN($B$4);RIJ(Lijst!$A$1:$A$16893);"");RIJ(A1)));"")

invoeren met CTRL-SHIFT + ENTER en dan een heel eind naar beneden doortrekken.

Hartelijk dank! Meteen bingo!

 

[Sanglan]

En ook de rest super bedankt voor de snelle reacties, nu kan ik weer verder!