Excel Formule:

[xchrisnl]

Beste,
Wie o wie kan mij helpen, ik ben al 2 dagen bezig verschillende formules aan het proberen om van 2 input data een controle te doen naar een ID nummer te gaan. Helaas krijg ik het niet voor elkaar.

Wat er moet gebeuren in de sheet;
Ik heb 2 data values (Omschrijving F, Omschrijving O), waarvan ik de ID codes moet krijgen.
Een Omschrijving O kan ook in meerdere voorkomen; zie foto voor verduidelijking

Dus de uitkomst van B12 zou dan moeten zijn, 2, 6



Wie o wie heeft de gouden tip/formule wellicht voor mij hoe ik dit zou kunnen oplossen, super bedankt alvast !

[Room42]

Dat zal denk ik met een macro moeten. Maar wat had je al gevonden en welke software (en taal) gebruik je? Want dat laat je heel stiekem gewoon maar weg?

[Flimovic]

Met vert.zoeken kan je toch B10 opzoeken in Kolom D? En B11 opzoeken in kolom E?

Dan kan je daarna de uitkomst van die 2 formules in B12 neerzetten?

Nope, gaat niet werken.

[ Voor 10% gewijzigd door Flimovic op 18-02-2020 14:44 ]

[tritimee]

hier staat je oplossing:
https://spreadsheeto.com/index-match/

ik doe het zelf meestal met offset en match in een arrayformule.

je zult een match krijgen met Match(1;(F:F=B10)*(E:E=B11);0) en vergeet geen ctrl shift enter te gebruiken om de formule als array formule op te zetten.

[ Voor 38% gewijzigd door tritimee op 18-02-2020 14:45 ]

[htca]

Ik zou 2 extra kolommen aanmaken:
- kolom F waarin je beide omschrijvingen concatenate
- kolom G een kopie van kolom A
- verticaal zoeken met een geconcatenate waarde van B10 en B11 in de array F2:G7

[JPM85]

Kijk eens naar de uitleg in onderstaande link.

https://www.extendoffice....te-based-on-criteria.html

Je zal dit hoogstwaarschijnlijk met VBA moeten doen, omdat je 2 celwaarde als uitkomt wilt samenvoegen.
Niet heel moeilijk, kost je een paar minuten om te doen.

[xchrisnl]

Super!, heb al wat geprobeerd en kom nu in de goeieweg ! Die hulp kolommen doen veel.

[tritimee]

Mooi!
Maar ze zijn wel compleet overbodig

offset(A1;Match(1;(D:D=B10)*(E:E=B11);0)-1;0) zou voldoende moeten zijn

xchrisnl schreef op dinsdag 18 februari 2020 @ 15:13:
Super!, heb al wat geprobeerd en kom nu in de goeieweg ! Die hulp kolommen doen veel.

[Lustucru]

tritimee schreef op dinsdag 18 februari 2020 @ 14:43:
hier staat je oplossing:
https://spreadsheeto.com/index-match/

JPM85 schreef op dinsdag 18 februari 2020 @ 14:54:
Kijk eens naar de uitleg in onderstaande link.

https://www.extendoffice....te-based-on-criteria.html

Toch jammer als er naar andere sites wordt verwezen als het goede antwoord ook op tweakers te vinden is:
F_J_K in "[Excel] combi van Index, Match, Search":

code:

{=INDEX(A1:A8;VERGELIJKEN(1;(B10=D2:D8)*(B11=E2:E8);0);1) }

Wat dus op hetzelfde neerkomt als

ik doe het zelf meestal met offset en match in een arrayformule.

je zult een match krijgen met Match(1;(F:F=B10)*(E:E=B11);0) en vergeet geen ctrl shift enter te gebruiken om de formule als array formule op te zetten.

of

offset(A1;Match(1;(D:D=B10)*(E:E=B11);0)-1;0) zou voldoende moeten zijn

Wat de matrix formule doet: hij vergelijkt alle waarden in beide kolommen. Zijn ze gelijk dan is dat 1, zijn ze niet gelijk dan levert dat 0. Die worden per regel vermenigdvuldigd en dan krijg je iets als {0;0;0;1;0;0;0}. Met vergelijken haal je positie op van de eerste '1' en met index pak je de juiste waarde uit de kolom waarin je het resultaat zoekt.

[JPM85]

Lustucru schreef op dinsdag 18 februari 2020 @ 18:30:
[...]


[...]


Toch jammer als er naar andere sites wordt verwezen als het goede antwoord ook op tweakers te vinden is:
F_J_K in "[Excel] combi van Index, Match, Search":

code:

{=INDEX(A1:A8;VERGELIJKEN(1;(B10=D2:D8)*(B11=E2:E8);0);1) }

Wat dus op hetzelfde neerkomt als

[...]


of


[...]


Wat de matrix formule doet: hij vergelijkt alle waarden in beide kolommen. Zijn ze gelijk dan is dat 1, zijn ze niet gelijk dan levert dat 0. Die worden per regel vermenigdvuldigd en dan krijg je iets als {0;0;0;1;0;0;0}. Met vergelijken haal je positie op van de eerste '1' en met index pak je de juiste waarde uit de kolom waarin je het resultaat zoekt.

Toch jammer als mensen de vraag niet goed lezen, want het enige dat jou matrix formule doet is de 1e waarde die beide 'TRUE' geven neer zetten. TS is niet alleen op zoek naar ID2 waar de 2 zoekwaarden gelijk aan zijn, maar ook naar ID6, waar de waarde PERE02 voorkomt als tekst in cel E7.

Daarnaast ben ik niet verantwoordelijk voor het zoeken op tweakers naar andere oplossingen die gelijk zijn aan de vraag van TS, dat kan hij lekker zelf doen.


@xchrisnl
In kolom F een hulp kolom toevoegen & onderstaande formule plakken en doortrekken tot cel F7 checkt welke ID rij een match aanwezig is in de tekst.

code:

=IFERROR(INDEX($A$2:$A$7;AGGREGATE(15;6;(ROW($A$2:$A$7)-ROW($A$2)+1)/ISNUMBER(SEARCH($B$11;$E$2:$E$7))/ISNUMBER(SEARCH($B$10;$D$2:$D$7));A2));"")

Afhankelijk van je versie van Excel kan je met de functie TEXTJOIN op basis van deze kolom de ID waarde's joinen in cel B12 tot text. Ik heb deze functie niet, dus ben aangewezen op VBA (google maar op VBA stringconcat, meerdere voorbeelden vindbaar)

Let op! functie is in het Engels

[ Voor 19% gewijzigd door JPM85 op 19-02-2020 15:05 ]

[dix-neuf]

JPM85 schreef op dinsdag 18 februari 2020 @ 14:54:
Je zal dit hoogstwaarschijnlijk met VBA moeten doen, omdat je 2 celwaarde als uitkomt wilt samenvoegen. Niet heel moeilijk, kost je een paar minuten om te doen.

Met VBA is een oplossing inderdaad goed te maken, maar op 2 minuten kan dat niemand.

code:

Sub macro1()
Dim result As String, x As Integer, a
a = 0: result = ""
With Sheets(1)
For x = 2 To 7
On Error Resume Next
If .Range("d" & x).Value = .Range("b10").Value Then
a = Application.Search(.Range("b11"), .Range("e" & x))
If a = 0 Then
GoTo volgende
Else
If Len(result) = 0 Then
result = .Range("a" & x).Value
Else
result = result & "," & .Range("a" & x).Value
End If
End If
End If
volgende:
Next x
.Range("g1").Value = result
End With
End Sub

[ Voor 40% gewijzigd door dix-neuf op 19-02-2020 21:41 ]

[xchrisnl]

Thnnxx allen! Ik heb het voor elkaar super bedankt allemaal!!!

[Room42]

xchrisnl schreef op donderdag 20 februari 2020 @ 07:55:
Thnnxx allen! Ik heb het voor elkaar super bedankt allemaal!!!

Wat is de volledige oplossing dan? Dan heeft de volgende er ook wat aan.

[tovade]

Heb de code van dix-neuf gebruikt om het iets generieker te maken. Hier houdt hij bv rekening met hoe lang de tabel is. Maar op zich doet het natuurlijk hetzelfde.

code:

Sub macro1()


Dim strResult As String

Dim rngZoek As Range


strResult = ""


With ActiveSheet


'doorloop kolom D tot je een lege cel tegen komt

For Each rngZoek In .Range(.Range("D1"), .Range("D1").End(xlDown))


    If rngZoek.Value = .Range("B10").Value Then

        'check of 'omschrijving O' voorkomt in de cel naast rngZoek. Funtie geeft een Error wanneer hij niets vindt

        If Not (IsError(Application.Search(.Range("B11").Value, rngZoek.Offset(0, 1).Value))) Then

            If strResult = "" Then

                'eerste resultaat.

                strResult = rngZoek.Offset(0, -3).Value

            Else

                'elk daaropvolgend resultaat

                strResult = strResult & ", " & rngZoek.Offset(0, -3).Value

            End If

        End If

    End If

Next


.Range("B12").Value = strResult


End With


End Sub

[dix-neuf]

tovade schreef op donderdag 20 februari 2020 @ 10:26:
Heb de code van dix-neuf gebruikt om het iets generieker te maken.
Hier houdt hij bv rekening met hoe lang de tabel is.

Nee, dat doet jouw wijziging niet noodzakelijk, want als er een lege cel in kolom D staat, dan wordt er bij die cel gestopt. Je kunt daarom beter code gebruiken die tot het eind van de kolom gaat, ongeacht of er lege cellen in staan of niet. Verder zie ik niet wat er zo generieker aan is.

[tovade]

Alles hangt er natuurlijk vanaf hoe de tabel is opgebouwd. In jouw code stopt hij gewoon op rij 7. Wordt de tabel groter, moet je je code aanpassen. Dat bedoelde ik dus met generieker.

In het geval dat de tabel mooi aansluit en er dus geen lege cellen zijn in de kolom waarin je zoekt, maakt het met mijn code niet uit hoeveel rijen er zijn.

Maar nogmaals, alles valt en staat met hoe de tabel is opgebouwd. Ik wilde gewoon een andere manier tonen om de dingen te benaderen.

[Lustucru]

JPM85 schreef op woensdag 19 februari 2020 @ 11:54:
[...]
TS is niet alleen op zoek naar ID2 waar de 2 zoekwaarden gelijk aan zijn, maar ook naar ID6, waar de waarde PERE02 voorkomt als tekst in cel E7.

Daar heb je een punt. En idd een probleem met teksten samenvoegen, vziw (maar wellicht in O365 is dat anders) accepteren de ingebouwde samenvoegfuncties geen ranges als parameter. Dan ontkom je idd niet aan VBA. Wat ik ooit voor iemand maakte, een UDF die voorwaardelijk samenvoegt:

Visual Basic:

Public Function SAMENVOEGENALS(samenvoegbereik As Range, Scheidingsteken As String, Optional criteriumbereik As Range, Optional criterium As Variant, Optional benaderen As Integer, _
    Optional criteriumbereik2 As Range, Optional criterium2 As Variant, Optional benaderen2 As Integer, _
    Optional criteriumbereik3 As Range, Optional criterium3 As Variant, Optional benaderen3 As Integer)
    'etc

    Dim result As String
    Dim source As Range
    Dim match As Boolean
    On Error GoTo errh
    
    For Each source In samenvoegbereik
        match = True
        If Not (criteriumbereik Is Nothing) Then CHECKMATCH match, criteriumbereik.Cells(source.Row, source.Column).Value, criterium, benaderen
        If Not (criteriumbereik2 Is Nothing) Then CHECKMATCH match, criteriumbereik2.Cells(source.Row, source.Column).Value, criterium2, benaderen2
        If Not (criteriumbereik3 Is Nothing) Then CHECKMATCH match, criteriumbereik3.Cells(source.Row, source.Column).Value, criterium3, benaderen3
        ' etc
        If match Then result = result & source & Scheidingsteken
    Next source
    
    If result > "" Then
        result = Left(result, Len(result) - Len(Scheidingsteken))
    End If
    
    SAMENVOEGENALS = result
    Exit Function
errh:
    SAMENVOEGENALS = Err.Description
End Function

Private Sub CHECKMATCH(ByRef match As Boolean, compareWith, curValue, approx As Integer)
    If match Then
        If approx <> 0 Then
            match = InStr(compareWith, curValue) > 0
        Else
            match = (compareWith = curValue)
        End If
    End If
End Sub

Aan te roepen als functie met een samenvoegbereik, gewenst scheidingsteken en optioneel maximaal drie criteria met aparte criteriumbereiken. De laatste parameter geeft per criteriumbereik aan of de waarde moet voorkomen in de cel of dat de cel het criterium exact moet bevatten.