[EXCEL] Deel van tekst zoeken in tabel*

[NeP2013]

Hallo!

Ik probeer met Excel een formule voor elkaar te krijgen, maar het lukt me niet. Wellicht denk ik te moeilijk. Hulp wordt zeer gewaardeerd.

Wat ik nodig heb:

Ik heb een lijst met computernamen in Tab A, in Kolom A.
In Tab B, heb ik een lijst met Computernamen, maar niet de volledige
In Tab A, Kolom B, wil ik uit Tab B het deel waarmee de computernaam BEGINT, matchen aan een waarde in kolom B op Tab B

Voorbeeld

TAB A

Computernaam, Code
NL-ABCDE
NL-DEFGH
NL-DFGHE

TAB B

Computernaam, Categorie
NL-D* -- Desktop

Gewenst resultaat

TAB A:
NL-ABCDE - Geen Desktop
NL-DEFGH - Desktop
NL-DFGHE - Desktop

Hoe kan ik dit op een simpele manier voor elkaar boxen?
Note: Heft hoeft niet zo te zijn dat Tab B, Kolom A NL-D* alleen beval, daarnaast kan het ook zo zijn dat er bijv NL-DER* of BE-L* in staat. De naam conventie is dus niet perse xx-x*, maar kan ook xxxx-x* zijn.

Hopelijk goed uitgelegd, iemand tips?!

[BIM]

Ik zou een hulpkolom maken, waarin je het eerste teken na "-" weergeeft en op basis daarvan bepaald of het een desktop is.

=mid(A1,find("-",A1,1)+1,1)

--- edit ---

Je kunt hem nog vollediger maken door te zeggen:

=if(mid(A1,find("-",A1,1)+1,1)="D","Desktop","Geen Desktop")

[ Voor 30% gewijzigd door BIM op 11-09-2018 16:30 ]

[g0tanks]

Hoeveel categorieën zijn er? Als het namelijk alleen om de eerste letter gaat na het koppelteken (b.v. D = desktop, L = laptop) dan hoef je natuurlijk alleen daar naar te kijken.

[naitsoezn]

Het maakt het complex omdat je in dit geval één lange string wilt matchen in een column met kortere strings (andersom was het makkelijker geweest), maar ik denk dat je met één hulp-kolom (kolom C in mijn formule hierbeneden) in Tab B en de volgende functie in Tab A (cell B2) een generieke functie hebt (die mogelijk nog te optimaliseren valt, maar dat laat ik aan jezelf )

code:

=IF(SUMPRODUCT( --ISNUMBER(SEARCH(TabB!$A$2:$A$4, $A2)), TabB!$C$2:$C$4)>0, INDEX(TabB!B:B, SUMPRODUCT( --ISNUMBER(SEARCH(TabB!$A$2:$A$4, $A2)), TabB!$C$2:$C$4)), "Geen Desktop")

De hulpkolom C in Tab B vul je simpelweg met "=ROW()". Deze formule gaat er vanuit dat je drie categorieën hebt in Tab B (rijen 2 t/m 4), wanneer dit meer zijn dan moet je de range in Tab B aanpassen (vergeet de hulpkolom niet).

[Lustucru]

@naitsoezn doet veel te moeilijk. Onder een klein aantal condities doet vert.zoeken, met benaderen aan, het ook. Belangrijk is dat codes geen deel van elkaar zijn en je je opzoektabel sorteert.

NL-D* is niet te onderscheiden van NL-Der*. Als je dat vermijdt is een simpel =VERT.ZOEKEN([vollenaam];[opzoektabel];2;1) voldoende.

Komt dit wel voor dan moet je eveneens de opzoektabel sorteren en kun je uit de voeten met de volgende matrix formule:

code:

{=INDEX(B1:B5;MAX(ALS(LINKS(C1;LENGTE(A1:A5))=A1:A5;RIJ(A1:A5);0)))}

waarin in B1:b5 de gezochte categorie staat, in A1:A5 de begincode en in c1 de volledige computernaam.

offtopic:
Verzin ajb de volgende keer een betere titel. [Excel] Formulevraag zegt helemaal niets namelijk: 99% van de excel topics is een vraag over een formule. Ook wordt het bijzonder op prijs gesteld als je in de startpost je eigen ideeën en pogingen uiteenzet.

[ Voor 93% gewijzigd door Lustucru op 11-09-2018 18:03 ]

[NeP2013]

Lustucru schreef op dinsdag 11 september 2018 @ 17:10:
@naitsoezn doet veel te moeilijk. Onder een klein aantal condities doet vert.zoeken, met benaderen aan, het ook. Belangrijk is dat codes geen deel van elkaar zijn en je je opzoektabel sorteert.

NL-D* is niet te onderscheiden van NL-Der*. Als je dat vermijdt is een simpel =VERT.ZOEKEN(~~~[vollenaam];~~~[opzoektabel];2;1) voldoende.

Komt dit wel voor dan moet je eveneens de opzoektabel sorteren en kun je uit de voeten met de volgende matrix formule:

code:

{=INDEX(B1:B5;MAX(ALS(LINKS(C1;LENGTE(A1:A5))=A1:A5;RIJ(A1:A5);0)))}

waarin in B1:b5 de gezochte categorie staat, in A1:A5 de begincode en in c1 de volledige computernaam.

offtopic:
Verzin ajb de volgende keer een betere titel. ~~~[Excel] Formulevraag zegt helemaal niets namelijk: 99% van de excel topics is een vraag over een formule. Ook wordt het bijzonder op prijs gesteld als je in de startpost je eigen ideeën en pogingen uiteenzet.

Inderdaad, had even wat duidelijker gekund, dank voor de feedback daarop.

Ook bedankt voor de formule, ik kom echter niet op het gewenste resultaat. Ik zie ook dat ik het wellicht niet volledig heb uitgelegd.

Ik heb een lijst van ruim 50.000 PC namen. Meerendeel van de namen van de PCs kan ik aan de naamconventie zien (BV: NL-1234 = NL, NEDL-234 = NL, ABCB* = Land Y, 123456* = land Z

Ik heb dus een Tabblad, Tabblad B, waarin ik al deze naamconventies heb staan met wildcards, aangezien die meerendeel bekend zijn. Het is dus niet per defenitie desktop/laptop, maar ook land.

De tabel in Tabblad B heeft dus 2 kolommen:

Naamconventie (+wildcard) in kolom A en categorie/land in kolom B.

NaamType	Categorie/Land
NL-1234*	Netherlands
NEDL-123*	Netherlands
SETL*	Letland

Echter zijn de groottes van de computernamen verschillend. Ene land heeft 5 karakters, andere 10, maar dmv de naamconventie met wildcard kan i k ze onderscheiden.

De formule die jij noemt, geeft me niet het gewenste resultaat, hij springt elke keer een regel verder. Wellicht doe ik iets fout hoor, heb nu dit:

code:

 =INDEX(NameType!$B$3:$B$5,MAX(IF(LEFT(A3,LEN(NameType))=NameType,ROW(NameType),0)))

Let op, ik heb van kolom A een named range "NameType" gemaakt

edit, heb ook de nametype even veranderd in de huidige range, maakte geen verschil (het is ook een Array door CTRL-SHIFT-ENTER te drukken, de brackets missen alleen even { } :

code:

 =INDEX(NameType!$B$3:$B$5,MAX(IF(LEFT(A3,LEN(NameType!$A$3:$B$5))=NameType,ROW(NameType!$A$3:$B$5),0)))

[ Voor 10% gewijzigd door NeP2013 op 12-09-2018 09:31 ]

[Lustucru]

Twee dingetjes: als je vert.zoeken gebruikt moet naamtype gesorteerd zijn (anders kan hij nooit benaderen) en in beide gevallen moet dat sterretje achter hetNaamtype weg. Het = teken ondersteunt geen wildcards, maar je gaf aan dat het altijd simpelweg de beginsequentie is.

[ Voor 82% gewijzigd door Lustucru op 12-09-2018 09:53 ]

[NeP2013]

Lustucru schreef op woensdag 12 september 2018 @ 09:46:
Drie dingetjes: als je vert.zoeken gebruikt moet naamtype gesorteerd zijn (anders kan hij nooit benaderen) en in beide gevallen moet dat sterretje achter hetNaamtype weg. Tenminste ik ga ervan uit dat het sterretje hier als jokerteken wordt gebruikt.

tot slot: kijk nog eens goed naar je ranges, want van

code:

=INDEX(NameType!$B$3:$B$5,MAX(IF(LEFT(A3,LEN(NameType!$A$3:$B$5))=NameType,ROW(NameType!$A$3:$B$5),0)))

klopt het e.e.a. niet.

Ik heb hem nu zo:

code:

 =INDEX(NameType!$B$2:$B$4,MAX(IF(LEFT(A3,LEN(NameType!$A$2:$A$4))=NameType!$A$2:$A$4,0)))

ik heb gesorteerd op A..Z in de range waar ik zoek, dus de NameType tab, op Naam. Echter krijg ik overal alleen het resultaat van regel 1 terug (dus de eerste regel, tweede kolom categorie).. hij is close, maar vermoed dat ik iets verkeerd heb gedaan.. de wildcard heb ik ook weg nu, dus alleen waar hij mee begint

[Lustucru]

NeP2013 schreef op woensdag 12 september 2018 @ 09:56:


Ik heb hem nu zo:

code:

 =INDEX(NameType!$B$2:$B$4,MAX(IF(LEFT(A3,LEN(NameType!$A$2:$A$4))=NameType!$A$2:$A$4,0)))

Ik mis wat in je als() functie. Veel meer dan 0 of onwaar krijg ik niet terug...

[NeP2013]

Lustucru schreef op woensdag 12 september 2018 @ 10:07:
[...]

Ik mis wat in je als() functie. Veel meer dan 0 of onwaar krijg ik niet terug...

ik ben bang dat ik je niet begrijp..

Maar wat mis je, bij 0, ... een criterium? of moet ik daar dan weer een vlookup in gooien om het in de tabB te vinden?

[Lustucru]

De uitleg van de hele formule {=INDEX(B1:B5;MAX(ALS(LINKS(C1;LENGTE(A1:A5))=A1:A5;RIJ(A1:A5);0)))}

code:

{LINKS(C1;LENGTE(A1:A5))=A1:A5)

vergelijk de naam met alle naamconventies. Het resultaat is een array bv {ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR}

code:

{(ALS({ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR};RIJ(A1:A5);0)

Als het array de waarde waar heeft, geef dan het rijnummer terug -dit mist bij jou-, anders 0
{0;0;3;4;0}

code:

{MAX({0;0;3;4;0})

Geeft 4

code:

{=INDEX(B1:B5;4)

En dat geeft het resultaat van regel 4 in het bereik B1:B5

[NeP2013]

Lustucru schreef op woensdag 12 september 2018 @ 10:16:
De uitleg van de hele formule {=INDEX(B1:B5;MAX(ALS(LINKS(C1;LENGTE(A1:A5))=A1:A5;RIJ(A1:A5);0)))}

code:

{LINKS(C1;LENGTE(A1:A5))=A1:A5)

vergelijk de naam met alle naamconventies. Het resultaat is een array bv {ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR}

code:

{(ALS({ONWAAR;ONWAAR;WAAR;WAAR;ONWAAR};RIJ(A1:A5);0)

Als het array de waarde waar heeft, geef dan het rijnummer terug -dit mist bij jou-, anders 0
{0;0;3;4;0}

code:

{MAX({0;0;3;4;0})

Geeft 4

code:

{=INDEX(B1:B5;4)

En dat geeft het resultaat van regel 4 in het bereik B1:B5

ik ben bang dat ik iets gruwelijk fout doe;

code:

 =INDEX(NameType!$B$1:$B$5,MAX(IF(LEFT(Data!A2,LEN(NameType!$A$1:$A$5))=NameType!$A$1:$A$5,ROW(NameType!$A$1:$A$5),0)))

krijg overal dezelfde waarde, zelfs als Array.. op de een of andere manier gaat het iig niet goed. en ik heb geen flauw idee..


EDIT: GEVONDEN!! ik had de Array gemaakt nadat ik de formule doortrok, ipv daarvoor. daardoor sprong regel A2 niet mee naar de volgende A3, A4, etc

Super bedankt voor je hulp!!

[ Voor 8% gewijzigd door NeP2013 op 12-09-2018 10:32 ]