Cel vullen o.b.v. setje woorden dat in de tekst in een cel voorkomt

[Martijnbm]

Goeiemiddag allemaal,

al enige tijd gebruik ik Excel om inzicht te krijgen in bedragen die met bepaalde activiteiten worden verdiend.
Ik heb een eenvoudige versie gemaakt om e.e.a. te verduidelijken. Zie bijlage.

Kolom A bevat een omschrijving van een handeling; kan iedere keer anders zijn.
Kolom B bevat een bedrag dat bij de activiteit hoort.
Kolom C geeft het bedrag uit kolom B weer indien de activiteit onder categorie 1 valt.
Kolom D geeft het bedrag uit kolom B weer indien de activiteit onder categorie 2 valt.
Kolom E geeft het bedrag uit kolom B weer indien de activiteit onder categorie 3 valt.

Ik kan hiermee zien hoeveel met activiteiten van categorie 1, 2 en 3 verdiend is.
Wat ik nu doe is de waarde uit kolom A lezen, vaststellen in welke categorie het valt en dan de formule in die cel plakken zodat het bedrag in de juiste categorie valt.
Dit is heel veel werk en ik zoek een manier om het te automatiseren.

In Blad2 heb ik de categorieën nog eens vermeld met daaronder enkele termen die bepalen tot welke categorie een activiteit behoort. Het komt niet voor dat termen uit verschillende categorieën in één omschrijving staan. Bij "auto, peer" zou er anders onduidelijkheid bestaan.

De velden onder Cat1, 2 en 3 in Blad1 zou ik dus van een formule willen voorzien die kijkt om welke categorie het gaat (de waarde in de cellen C1, D1 of E1). Vervolgens moet in kolom A gezocht worden naar woorden die in Blad2 onder de betreffende categorie zijn gezet. Wordt de term gevonden, dan moet de waarde uit kolom B worden vermeld. Wordt de waarde niet gevonden, dan moet de cel leeg blijven.

Het zou me heel veel tijd schelen indien ik hiervoor de juiste formule kan toepassen.
Alvast bedankt voor jullie hulp!

Groet,

Martijn

 

[Rubenio]

Here you go :thumb::thumb:

Op blad 2 heb ik de activiteiten wel hetzelfde moeten omschrijven (volledige omschrijving)

 

[Martijnbm]

Dank je Rubenio,

het punt is echter dat de omschrijving soms uit hele zinnen bestaat die steeds anders zijn.
Het is de bedoeling dat het bedrag onder een categorie komt indien de bijbehorende term ergens in de tekst voorkomt.

Ik hoop dat dit ook gaat lukken!

 

[HSV]

Met een UDF.
Macro"s moeten ingeschakeld zijn.

 

[Martijnbm]

Sorry voor de late reactie.
Ik heb het bestand bekeken en volgens mij is dit precies wat ik zoek. Even een filmpje bekeken over het maken van een udf maar dat is nog niet zo makkelijk.

Bedankt voor de hulp!!

 

[Martijnbm]

Waarom is het trouwens een binaire werkmap i.p.v. een gewone?

 

[HSV]

Een .xlsm en .xlsb kan alleen macro's bevatten.
De laatst genoemde is sneller in het laden en opslaan (hier op het forum mag het bestand ook nog meer dan 100 kb bevatten dan de standaard).

 

[Martijnbm]

Toch weer een vraag. Ik heb de udf gekopieërd naar een ander Excel bestand en kreeg de functie ogenschijnlijk aan de praat, maar slechts bij één cel.
Vervolgens heb ik alle tabbladen van mijn werkmap met echte waarden overgezet naar het bestand waar je de udf in gezet hebt maar dat hielp niet.

Nu heb ik in het door jou geposte bestand wat regels ingevoegd, zie bijlage, en die geven ook geen resultaat.
Is er een instelling verkeerd in Excel of zie ik iets over het hoofd?

 

[VenA]

Je moet het bereik in de formule wel aanpassen. Beter is het om gebruik te maken van een tabel. Dan heb je hier geen omkijken naar.

 

[Martijnbm]

Dank je VenA, het bereik was idd niet goed.

Ik zie nu wat er verder aan mankeert:
Het moet zo zijn dat de exacte celinhoud van Blad 2 gezocht wordt in de omschrijving op Blad 1. Nu is het zo dat de losse woorden in de omschrijving op Blad 1 gezocht worden in de categorieën op Blad 2.

Stel, in de omschrijving staat "peer", dan komt dat overeen met Cat. 1. op Blad 2.
Verander ik "peer" in de omschrijving in "peertje", dan is er geen match en dat hoort wel. Op Blad 2 staat immers "peer" en dat komt voor in "peertje".

Hoe krijg ik dit omgedraaid?

 

[HSV]

Zet deze code er eens achter.

Function hsv_zoekwoord(zoek As Range, bereik As Range, Cat As Range)
Dim sq, c As Range, i As Long, c00 As String, j As Long, jj As Long
sq = Split(zoek)
 For i = 0 To UBound(sq)
   For j = 1 To 3
    c00 = Join(Application.Transpose(bereik.Columns(j)), "|") & "|"
      For jj = 1 To Len(sq(i))
     If InStr(c00, Mid(sq(i), 1, jj) & "|") > 0 Then
       Set c = bereik.Find(Mid(sq(i), 1, jj), , , 1)
        If Cat = Sheets("blad2").Cells(1, j) Then
          hsv_zoekwoord = zoek.Offset(, 1)
          Exit Function
        End If
       End If
     Next jj
     Next j
  Next i
End Function

Met in de cel de formule:

=ALS.FOUT(hsv_zoekwoord($A2;Table1;C$1);"")

 

[Martijnbm]

Ik heb de functie aangepast.
Nu vallen sommige bedragen in 2 categorieën, terwijl er geen zoekwoorden in de betreffende categorie staan.

Voorbeeld: slaapkamer gepoetst komt in 2 categorieën voor, terwijl er alleen cellen in cat 3 staan die voorkomen in de omschrijving, namelijk "slaapkamer"...

 

[HSV]

In de herkansing.

Function hsv_zoekwoord(zoek As Range, bereik As Range, Cat As Range)
Dim sq, sn, c As Range, i As Long, c00 As String, j As Long, jj As Long, jjj As Long
sq = Split(zoek)
 For i = 0 To UBound(sq)
   For j = 1 To 3
    sn = Split(Join(Application.Transpose(bereik.Columns(j)), "|"), "|")
     For jj = 0 To UBound(sn)
      For jjj = 1 To Len(sq(i))
     If sn(jj) Like Mid(sq(i), 1, jjj) Then
       Set c = bereik.Find(Mid(sq(i), 1, jj), , , 1)
        If Cat = Sheets("blad2").Cells(1, j) Then
          hsv_zoekwoord = zoek.Offset(, 1)
          Exit Function
        End If
       End If
      Next jjj
     Next jj
     Next j
  Next i
End Function

 

[VenA]

Andersom gedacht geeft volgens mij wat minder lusjes. Waarbij een offset in een Function een beetje discutabel is.

Function VenA(zoek As Range, bereik As Range, Cat As Range)
Dim ar, j As Long, jj As Long
ar = bereik
For j = 1 To UBound(ar)
    For jj = 1 To UBound(ar, 2)
        If InStr(zoek, ar(j, jj)) And Cat = ar(1, jj) Then
            VenA = zoek.Offset(, 1)
            Exit Function
        End If
    Next jj
Next j
End Function

 

[HSV]

Slim:

Of gewoon een formuletje.

=ALS.FOUT(ALS(ZOEKEN(WAAR;VIND.SPEC(Tabel1[[#Alles];[Cat. 1]];$A2)>0);$B2);"")

 

[Martijnbm]

En welke code hoort nu bij welk bestand?

 

[Martijnbm]

Ik ben de afgelopen tijd bezig geweest (af en toe) om de code goed toegepast te krijgen maar het is me niet gelukt.

In het bestand waarin ik de udf wil gebruiken staat het bedrag links van de omschrijving, wellicht is het daar mis gegaan.
Ik heb in de bijlage het bestand iets aangepast.
Het zou me veel tijd schelen indien het voor elkaar komt.

Veel dank alvast!

 

[VenA]

Er staat geen UDF in het bestand. Dus welke van de suggesties en hoe je het hebt toegepast is er niet uit op te maken. Je moet het bestand opslaan als .xlsm of .xlsb anders ben je de code kwijt.

 

[HSV]

De formule versie.

 

[Martijnbm]

Dank voor je antwoord HSV,

In de bijlage heb ik jouw bestand gezet, waarbij ik in Blad 1 het woord appel heb vervangen door appl. Dan blijven de cellen in die rij leeg, zoals het hoort.
Voeg ik daarna het woord appl toe in Blad 2 onder Cat.1, dan worden de bedragen ineens weergegeven in Cat.2 en Cat.3, ook waar dat niet hoort.

Zou je daar nog even naar willen kijken?
Indien ik iets toevoeg op Blad2, dan breidt de tabel vanzelf uit, toch?

Alvast dank!