Zoeken op 2 voorwaarden, waarvan 1 een gedeelte van een cel inhoud.

[rozebooz]

Goedemorgen,


Ik weet niet zeker of dit mogelijk is Zelf was ik al aan wezen prutsen zonder succes helaas.

Ik heb een kolom met cellen waarin een factuur nummer staat. Enkel soms staat er ipv enkel een factuurnummer geen "100", maar "factuur 100". Dit kan per keer verschillen, maar dus tekst er omheen. In een ander blad staat een lijst met factuurnummers en dan altijd enkel het nummer. Graag wil ik zoeken naar een match ondanks de extra tekst. Daarnaast als met de formule de rij is gevonden die match aan het factuur nummer, wil ik het bedrag die in die rij staat extra laten controleren met het bedrag die bij de rij in blad "bank" staat of het overeenkomt. Gewoon voor de zekerheid

Ik heb een voorbeeld bijgevoegd waarin hij nu dus de foute rij pakt en dit moet de groene rij in blad "itemsales" zijn. Met tekst en groen heb ik in het voorbeeld extra aangegeven waar de formule moet komen te staat waar het om gaat en waaraan hij zou moeten matchen. Als dit allemaal klopt zou de formule "match" moeten laten zien in de cel.
De oplossing zou mij weer iets extra handwerk uit handen nemen.


Bekijk bijlage bank match factuurnummer bedrag.xlsx

 

[SjonR]

met VBA kom ik hierop:

Sub SJonR()
    Ar1 = Sheets("Bank").UsedRange
    Ar2 = Sheets("Itemsales").UsedRange
        For i = 2 To UBound(Ar1)
            For j = 2 To UBound(Ar2)
                If InStr(Ar1(i, 1), Ar2(j, 2)) > 0 And Ar1(i, 4) = Ar2(j, 6) Then
                    Ar1(i, 13) = "MATCH"
                End If
            Next
        Next
Sheets("Bank").UsedRange = Ar1
End Sub

 

[AlexCEL]

Met formule:

M10: =ALS(SOMPRODUCT((ItemSales!$B$2:$B$1000=--DEEL($A10;VIND.SPEC("2";$A10);6))*(ItemSales!$F$2:$F$1000=$D10));"Match";"")

 

[rozebooz]

Dank je wel SjorR voor het meedenken! Ik ben behoorlijk onbekend met VBA, tot nu toe 1 keer eerder geprobeerd dat ging helemaal mis, enkel weet ik niet wat ik fout doe maar ergens in excel staat er iets niet goed. Zo net zag ik iets gebeuren, dus ik blij en vervolgens open ik opnieuw het bestanden en geeft hij aan "vanwege uw beveiligingsinstellingen zijn macro uitgeschakeld" terwijl ik dit volgens mij heb aangepast.. Dus er gebeurt niks meer. Het bestandje die je mee hebt gestuurd in je antwoord staat ook geen andere formules meer in als ik deze download. Ik wil in de toekomst zeker nog eens in verdiepen hoe VBA werk

Alexcel, Dank je wel! Dit werkt voor mij mooi en dit kan ik makkelijker aanpassen in mijn grote bestand. Nu had ik enkel 1 formule over het hoofd gezien, namelijk deze (macro) formulie die er naast staat en de rijnummer aangeeft.: =ALS.FOUT(VERGELIJKEN(Bank!A10;ItemSales!B:B;0);" ") jou code =ALS(SOMPRODUCT((ItemSales!$B$2:$B$1000=--DEEL($A10;VIND.SPEC("2";$A10);6))*(ItemSales!$F$2:$F$1000=$D10));"Match";""). wat moet ik hier veranderen om ipv match weer het rijnummer weer te geven?

Ik zie trouwens dat de formule niet werkt als de factuurnummer bijvoorbeeld NCD2018001 is in plaats van enkel cijfers. Opzich kan ik hier wel rekening mee houden en dit aanpassen dat mijn factuurnummers zo zijn maar ik denk te begrijpen dat de formule enkel dus een factuur nummer met cijfers kan herkennen en matchen, hoe komt dit als ik vragen mag?

 

[AlexCEL]

Zo bijv:

=ALS.FOUT(1/(1/SOMPRODUCT((ItemSales!$B$2:$B$1000=--DEEL($A10;VIND.SPEC("2";$A10);6))*(ItemSales!$F$2:$F$1000=$D10)*RIJ($2:$1000)));"")

Het 1/(1/..) gedeelte zit erin om nulwaarden eruit te filteren.

Hoe staat NCD2018001 vermeld in de lijst op tabblad "itemsales"? Als 2018001? Dan zou het gewoon goed moeten gaan. NCD2018001 is trouwens geen nummer maar een kenmerk (=tekst). De formule houdt rekening met tekst, zoals uit de regels met "factuurnummer ..." blijkt.

 

[rozebooz]

Dank je wel AlexCel, de formule werkt !

Soms er in tabblad "bank" een korte regel tekst omschrijving bij de factuurnummer of een datum. Informatie die ik wil matchen aan de regel/betaling komt uit meerdere bronnen "itemsales" & "bonnen" , vandaar dat ik dacht dat het misschien handig was om hier extra onderscheid of herkenningspunt in te maken, zodat ik weet waar het vandaan komt of waar het mee te maken heeft. Bijvoorbeeld een app die ik gebruik voor bonnen een afkorting te geven zoals "tap" en dan bonnen "tap2018001" te noemen en verder oplopend en mijn klanten systeem "NCD2018001" en dan oplopend, zo zal het dan ook netjes in de tabblad "itemsales" komen te staan. maandelijkste kosten zoals bijvoorbeeld de huur, zou ik ook nog huur01 voor januari kunnen noemen. ik hoop dat ik het zo goed uitleg. Er zal dus altijd
als er een match zou zijn een stukje tekst van de omschrijving in tabblad "bank" van een betaling overeenkomen met de gegeven kenmerk/factuurnummer uit de bronnen. Dan werk ik dus op dat moment met kenmerken en zal met deze formule niet kunnen. Misschien denk ik nu te uitgebreid en te ingewikkeld. Met de formules hoe ze nu werken zou ik ook al prima kunnen werken en scheelt mij al weer een stuk werk