Zoeken in cel op gedeelte tekst

[Imod]

Hallo,

Ik heb een probleempje,

Zie bijlage Bekijk bijlage zoeken.xlsm

Ik wil in 1 cel met een formule (geen VBA) een uitkomst hebben die overeen komt met
tekst uit een kolom wat overeenkomt uit de naast gelegen kolommen.
Zie voorbeeld - het zal denk ik met Index en vergelijken zijn in een matrixformule
Ik heb veel geprobeerd maar kom er niet uit!

 

[spaarie]

Voor A9 op Blad2

=als.fout(deel(d9;min(vind.spec({1;2;3;4;5;6;7;8;9};d9&"123456789"))-1;1);"")

 

[Imod]

Spaarie,

Dit werkt perfect zo te zien,:thumb:
Maar begrijpen doe ik het niet
Ik zie dat er een gedeelte matrix in zit,
Misschien een kleine uitleg??
Ik zie ook geen bereiken staan waar de gegevens weggehaald worden!
Als blad 1 - er staan nu 5 regels maar als dit er 30 worden??

 

[spaarie]

Het maken is vaak simpeler, al oogt het niet zo, dan het uitleggen hiervan.

Kort uitgelegd; met VIND.SPEC (vind.spec({1;2;3;4;5;6;7;8;9};d9&"123456789")) zoek ik in een opgegeven cel naar een numerieke waarde en krijg dan de positie van deze terug.
Met DEEL (deel(d9;min(vind.spec({1;2;3;4;5;6;7;8;9};d9&"123456789"))-1;1)) haal ik de letter vervolgens eruit (dat is dus de positie van de vind.spec formule -1)
ALS.FOUT is als dit hierboven een fout veroorzaakt (door bijvoorbeeld geen numerieke waarde in de zin) dan geeft ie een lege cel weer.

Wat makkelijker is om het te begrijpen, ontleed de formule zelf (heeft mij ook veel geholpen en doet het nog steeds). Dus zet in een cel =vind.spec({1;2;3;4;5;6;7;8;9};d9&"123456789") en kijk wat het resultaat is.
zet in de cel ernaast =DEEL(cel met zin;cel met uitkomst vind.spec;1) etc, zo zie je exact wat welke formule doet...

Nu dat ik het zo typ denk ik dat het niet goed over gaat komen, maar het is een poging tot

 

[Imod]

Spaarie,

Ik heb de formule ingevoerd in een andere grote sheet met veel formules en nog meerdere bladen,
Ik krijg de melding kringverwijzing!

Er kunnen ook dezelfde zoek woorden voorkomen die op meerdere regels en meer keren op dezelfde regel
en ook kunnen ze op andere tabbladen voorkomen!!

Misschien kun je de formule wat meer op specifieke kolommen en adressen laten gelden??
Blad1 blijft ongeveer hetzelfde maar dan met veel meer regels

 

[spaarie]

Imod,
Als je beide verwijzingen naar de cel D9 in de formule veranderd naar de jouw cel dan moet het goed gaan.
Plaats anders een voorbeeld die voldoet aan je opmaak, want ik kan niet reproduceren wat jij voor je ziet...

 

[Imod]

Spaarie,

Het bestand is te groot om dit hier neer te zetten,

Ik denk dat ik de kringverwijzing heb gevonden omdat de gegevens komen uit een tekst die is samengevoegd door een formule.
Maar nu heb ik een paar woorden gewijzigd en nu werkt het niet meer.
Is het nu zo dat er Letters en cijfers in moeten zitten??
Zie voorbeeld Bekijk bijlage zoeken1.xlsm

 

[arrie23]

Ik vrees dat Spaarie niet helemaal heeft begrepen wat de bedoeling is (wat ik gezien je uitleg en voorbeeldbestand eerlijk gezegd niet zo heel vreemd vind).
Wat de formule van Spaarie doet is op zoek gaan naar het eerste cijfer in de reeks en als resultaat retourneert hij dan de letter die voor dit cijfer staat.
Volgens mij wil jij iets heel anders (gebruik makend van de tabel op blad 1).Mijn advies is om een iets beter voorbeeldbestand te maken en het nog iets beter uit te leggen.
In je tweede bestand staat bijvoorbeeld op Blad1 het woord 'zal' zowel bij de A als bij de E, wat moet dan de uitkomst van de formule zijn?

 

[Imod]

Hallo Arrie23,

De bijlage zoeken1 klopt wel,
Voor het testen heb ik er wat woorden er ingezet - er staat nu per ongelijk 1 woord dubbel in
maar er komen GEEN dubbelen in de 6 kolommen B t/m G blad1 te staan.(alle regels)
Ik zal proberen uitleg te geven:
Waarop gezocht moet worden is kolom D - blad3 - dit is dmv de formule tekst samenvoegen een tekst aaneen of los van elkaar.
In kolom A blad3 komt een antwoord dat komt uit kolom A - blad1 komt.
Als er 1 woord of cijfer reeks uit kolommen D t/m G - blad1 overeen komt met de tekst uit kolom D blad3
dan moet kolom A blad3 de tekst geven uit de regel voor de 6 kolommen B t/m G in kolom A blad1 staat.(wat overeen komt)
In kolommen B t/m G kan in 1 cel wat staan of in meerdere cellen.(als er maar 1 woord overeenkomt is het al goed)

 

[Imod]

Met =ZOEKEN(99^99;1/(ISGETAL(VIND.ALLES(Blad1!$B$2:$B$6;Blad3!D9)));Blad1!$A$2:$A$6) in A9 werkt de formule wel
op de eerste cel op blad 1 kolom B.
Wie kan deze verder uitbouwen zodat er gezocht wordt op een woord of cijfers in de kolommen B t/m G??
Zie bijlage! Bekijk bijlage zoeken2.xlsm

 

[spaarie]

Mag het ook met VBA?

Sub Spaarie()
    Set b1 = Sheets("Blad1").Range("B2:G" & Sheets("Blad1").Range("B" & Rows.Count).End(xlUp).Row)
    Set b2 = Sheets("Blad3").Range("D9:D" & Sheets("Blad3").Range("D" & Rows.Count).End(xlUp).Row)
    
    For Each v1 In b1
        If v1 <> "" Then
            For Each v2 In b2
                If InStrRev(v2, v1) > 0 Then
                    If Sheets("Blad3").Cells(v2.Row, "A").Value = "" Then Sheets("Blad3").Cells(v2.Row, "A").Value = Sheets("Blad1").Cells(v1.Row, "A").Value
                End If
            Next
        End If
    Next
End Sub

 

[Imod]

Spaarie,

Dat mag wel,
Maar als een woord niet voorkomt in de kolommen B t/m G moet ik de code wel kunnen overrulen.
Er zijn regels die niet direct ergens bij kunnen en die moet ik per regel dan aanpassen liefst met een keuze uit een lijst.
Geen woord gevonden dan bv tekst erin "niet gevonden"
Ik heb je code in blad 3 gezet en het doet niets - ook in een module geprobeerd maar weer niets!! ???

 

[spaarie]

Het enige wat ie niet doet is 'niet gevonden' invullen...

 

[Imod]

Spaarie,

Ik heb de code getest op mijn eigen blad en dit werkt uitstekend!!:thumb::d:

Mijn dank is groot - dit bespaard mij heel veel werk en gezoek!:thumb: