Gegevens matchen

Boedashake · 1 jun 2017

Hallo,

Ik zit met een probleempje met het matchen van veel gegevens die ik vertaald wil hebben. Nu wil ik eerst alle dubbele eruit halen zodat ik deze zelf met bijv. verticaal zoeken kan matchen, nederlands vs frans enz. Echter nu heb ik ook veel artikelnamen die hetzelfde zijn maar waar een maat achter staat. Hieronder een aantal voorbeelden van artikelnamen die identiek zijn.

Hendel anker 1,1 mm 5 cm
Hendel anker 2,1 mm 8 cm
Hendel anker 2,4 mm 8,5 cm

Hevel palmier 4 mm 2 cm
Hevel palmier 6 mm 5 cm

Wat ik natuurlijk wil is dat ik alleen Hevel anker en Hevel palmier opstuur, anders betaal ik 5 keer ipv 3 keer. Echter als ik alleen hevel anker laat vertalen en ik wil dit matchen met verticaal zoeken dan herkend hij dit niet want er staat nog een maatvoering achter. Dus de vertaling zou zijn:

Veld A NL Veld B FR
Hendel anker ancre de levier

dan wil ik in een ander werkblad dit gaan matchen:

Veld A - Veld B
Hendel anker 1,1 mm 5 cm - ancre de levier 1,1 mm 5 cm (kan dit met excel? want met verticaal zoeken matcht hij nu niks)
Hendel anker 2,1 mm 8 cm
Hendel anker 2,4 mm 8,5 cm

En ik heb zo een 20.000 van dit soort gevallen, die ik nu niet kan matchen.Zoeken en vervangen is geen optie, dit is te veel werk. Misschien met een macro, HELP!!!

Hier een test bestandje waarbij blad 1 de vertaling is en waar blad 2 door gevoed moet worden.

Bekijk bijlage test.xlsx

ExcelAmateur · 1 jun 2017

Je ben je voorbeeld bestandje vergeten te plaatsen.

Boedashake · 1 jun 2017

Bestandje toegevoegd

Ik heb het bestandje toegevoegd.

VenA · 1 jun 2017

Met een druk op de blauwe knop.

Peter B · 1 jun 2017

Als de naamgeving altijd op deze manier is kan het (i.i.g.) met deze formule. Er zijn vast slimmere/ kortere oplossingen te bedenken:

Code:

ENG: =INDEX(Blad1!$B$2:$B$6;MATCH(LEFT(A2;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-2);Blad1!$A$2:$A$6;0))&RIGHT(A2;LEN(A2)-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))+2)

Boedashake · 1 jun 2017

VenA zei:
Met een druk op de blauwe knop.

Dit was mij ook gelukt, bedankt voor de effort. Maar achter de vertaling moeten dan wel de maten 1,1 mm 19 cm toegevoegd worden aan de vertalingen.

AlexCEL · 1 jun 2017

Zoiets?

Code:

B2: =INDEX(Blad1!$B:$B;MIN(ALS(ALS.FOUT(VIND.SPEC(Blad1!$A$2:$A$6;A2);0)>0;RIJ($2:$6);10^99)))&RECHTS(A2;LENGTE(A2)-LENGTE(INDEX(Blad1!$A:$A;MIN(ALS(ALS.FOUT(VIND.SPEC(Blad1!$A$2:$A$6;A2);0)>0;RIJ($2:$6);10^99)))))

Matrixfunctie, dus afsluiten met Control+Shift+Enter.

VenA · 1 jun 2017

Verander

Code:

ar1(j, 2) = ar(jj, 2)

in

Code:

ar1(j, 2) = ar(jj, 2) & Replace(ar1(j, 1), ar(jj, 1), "")

Boedashake · 12 jun 2017

Ik ben er bijna!

@VenA ik heb jou macro gebruikt en het werkt goed! Ik ben al een stuk verder mijn dank daarvoor.:thumb:

Ik loop echter tegen nog wat problemen tegen die ik niet zelf kan oplossen.
Bij sommige terugkerende artikelnamen die nog een derde woord erachter krijgen vertaald hij het derde woord niet.

Bijvoorbeeld de eerste en tweede vertaling is:
Hevel poli > Leffer poli
Hevel poli geel > Leffer poli yellow ( Echter wordt dit nu Leffer poli geel)

Echter bij de vertaling waarbij de maten erachter moeten komen vertaald hij nu geel niet.
Terwijl hij elders wel 3 woorden vertaald. Dit komt volgens mij omdat hij bovenaan al reeds eenmaal vertaald is en vervolgens geel niet meer vertaald.

In de bijlage heb ik de voorbeelden rood gemaakt.

Ook komt het voor dat de maten tussen de woorden staan:
Rotor pvc (gehard) > Rotor pvc (hardend) en hier moet hij dan eigenlijk de maten ertussen zetten.
Dit moet namelijk worden Rotor pvc 50 x 50 mm (hardend)

Deze opbouw kan wel 100tallen keren voorkomen met verschillende producten en wil ik maar een keer laten vertalen.

Hier loop ik dus op vast, ik hoop dat iemand dit kan tackelen. Als dit lukt kan ik het naar het vertaalbureau sturen.

Ik hoop dat ik duidelijk genoeg ben in mijn uitleg, als ik het nalees denk ik hmmmm....

Bekijk bijlage test2.xlsb

Bekijk bijlage Forum.xlsb

Peter B · 12 jun 2017

Omdat er gevallen zijn waar geen cijfers (maten) worden gevonden heb ik mijn oorspronkelijke formule voorzien van een IFERROR:

Code:

ENG: =IFERROR(INDEX(Blad1!$B$2:$B$20;MATCH(LEFT(A2;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-2);Blad1!$A$2:$A$20;0))&RIGHT(A2;LEN(A2)-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))+2);INDEX(Blad1!$B$2:$B$20;MATCH(A2;Blad1!$A$2:$A$20;0);0))
NLD: =ALS.FOUT(INDEX(Blad1!$B$2:$B$20;VERGELIJKEN(LINKS(A2;MIN(VIND.SPEC({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-2);Blad1!$A$2:$A$20;0))&RECHTS(A2;LENGTE(A2)-MIN(VIND.SPEC({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))+2);INDEX(Blad1!$B$2:$B$20;VERGELIJKEN(A2;Blad1!$A$2:$A$20;0);0))

Dit gaat goed, mits er een match is. Dat betekent dat de teksten wel exact hetzelfde moeten zijn, en niet zoals op regel 5 "Hevel poli mat" en in de vertaaltabel "Hevel poli/mat".

snb · 12 jun 2017

Dit lijkt mij de simpelste methode:

Code:

Sub M_snb()
  sp = Blad1.Cells(1).CurrentRegion
    
  With Blad2
    .Columns(1).RemoveDuplicates 1
    With .Cells(1).CurrentRegion
      .Offset(, 1) = .Value
    End With
        
    For j = 2 To UBound(sp)
      .Columns(2).Replace sp(j, 1), sp(j, 2)
    Next
  End With
End Sub

Boedashake · 12 jun 2017

Bedankt

Ik ga hier mee aan de slag, ik laat weten of het gelukt is.
Helemaal top! :love::thumb:

Wat een lange stukken code overigens tjeee!

Boedashake · 13 jun 2017

Uitzondering in de regel....

Peter B zei:
Omdat er gevallen zijn waar geen cijfers (maten) worden gevonden heb ik mijn oorspronkelijke formule voorzien van een IFERROR:

Code:

ENG: =IFERROR(INDEX(Blad1!$B$2:$B$20;MATCH(LEFT(A2;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-2);Blad1!$A$2:$A$20;0))&RIGHT(A2;LEN(A2)-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))+2);INDEX(Blad1!$B$2:$B$20;MATCH(A2;Blad1!$A$2:$A$20;0);0)) NLD: =ALS.FOUT(INDEX(Blad1!$B$2:$B$20;VERGELIJKEN(LINKS(A2;MIN(VIND.SPEC({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-2);Blad1!$A$2:$A$20;0))&RECHTS(A2;LENGTE(A2)-MIN(VIND.SPEC({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))+2);INDEX(Blad1!$B$2:$B$20;VERGELIJKEN(A2;Blad1!$A$2:$A$20;0);0))

Dit gaat goed, mits er een match is. Dat betekent dat de teksten wel exact hetzelfde moeten zijn, en niet zoals op regel 5 "Hevel poli mat" en in de vertaaltabel "Hevel poli/mat".

Ik heb de code gebruikt en dit is wat ik zoek!. Hij doet het haast (echt op het grootste aantal gaat het goed) op al mijn artikelnamen.
Er is nu nog een uitzondering die niet goed gaat, dit betreft artikelnamen met de opbouw "Hevel poli/mat 10 x 50 mm vlak" > De vertaling heb ik ontdaan van de maten zodat niet hetzelfde steeds vertaald hoeft te worden.
De vertaling is als volgt opgebouwd Hevel poli/mat vlak vertaling hiervan is Leffer poli/mat flat en moet dan gematcht worden met Hevel poli/mat 10 x 50 mm vlak waarbij het automatisch moet worden in de vertaling Leffer poli/mat 10 x 50 mm flat.

Ik heb een voorbeeld bestandje erbij gedaan, excuses maar de code is dusdanig ingewikkeld dat ik er zelf niks mee kan.....

Bekijk bijlage woord_maten_woord.xlsx

Hier nog een rottige opbouw

Combinatiehevel bestaande uit 5 losse hevels > Hier zou ik de 5 weghalen want hier gaat het van 2 tot 20 losse hevels en ik wil dit slechts 1 keer laten vertalen.
Dit zou dan worden in het bestand
Combinatiehevel bestaande uit losse hevels> Vertaling > Combinationleffers consists of loose leffers > Matchen > Combinatiehevel bestaande uit 5 hevels >vertaling moet dan automatisch worden > Combinationleffers consists of 5 loose leffers.

Peter B · 13 jun 2017

Hmmmm. Voor mij wordt dit te complex voor een formule. Ik denk dat het alsnog VBA moet gaan worden. Wat mijn formule nl. doet is alles tot aan het eerste getal (minus spatie) pakken en hierop zoeken. Vervolgens wordt de gezochte tekst voorzien van het gedeelte na het eerste getal (ik meende alleen de maataanduiding, maar kennelijk dus ook nog een type zoals bol of vlak.

Natuurlijk valt ook het gedeelte na het laatste getal te vinden, echter heb je dus ook nog te maken met maataanduidingen (mm; misschien ook andere maataanduidingen?) en moet je onderscheid kunnen maken welk gedeelte na het getal/ de getallen je wilt gebruiken om te zoeken en welk gedeelte niet.

Ik denk dat als je aan kunt geven welke maataanduidingen er zijn (mm, cm, m, ml, etc.) iemand hier binnen no-time een VBA-oplossing voor heeft.

snb · 13 jun 2017

@PB

#11 over het hoofd gezien ?

Boedashake · 13 jun 2017

Peter B zei:
Hmmmm. Voor mij wordt dit te complex voor een formule. Ik denk dat het alsnog VBA moet gaan worden. Wat mijn formule nl. doet is alles tot aan het eerste getal (minus spatie) pakken en hierop zoeken. Vervolgens wordt de gezochte tekst voorzien van het gedeelte na het eerste getal (ik meende alleen de maataanduiding, maar kennelijk dus ook nog een type zoals bol of vlak.

Natuurlijk valt ook het gedeelte na het laatste getal te vinden, echter heb je dus ook nog te maken met maataanduidingen (mm; misschien ook andere maataanduidingen?) en moet je onderscheid kunnen maken welk gedeelte na het getal/ de getallen je wilt gebruiken om te zoeken en welk gedeelte niet.

Ik denk dat als je aan kunt geven welke maataanduidingen er zijn (mm, cm, m, ml, etc.) iemand hier binnen no-time een VBA-oplossing voor heeft.

Bedankt voor je reactie, ik begrijp dat het pittig is, het gaat mij al ver boven mijn pet. Tijd dat ik mij eens ga verdiepen in dit soort oplossingen, tijd voor een cursus

In mijn bestanden heb ik te maken met de volgende maatvoeringen:

mm > 8 mm > 10 x 80 mm > 0,8 mm enz.
cm > 19 cm > 19 + 2 cm enz.
.ct > 0.10 .ct enz

En artikelnamen met opbouw zoals in de bestanden:
Hevel poli > Leffer poli > vertalen > Hevel poli 50 x 100 mm > wordt automatisch > Leffer poli 50 x 100 mm
Hevel poli bol > Leffer poli sphere > vertalen > Hevel poli 50 x 100 mm bol > wordt dan automatisch (zou moeten) > Leffer poli 50 x 100 mm sphere
Hevel poli bolle buis > Leffer poli sphere tube> vertalen > Hevel poli 50 x 100 mm bol buis> wordt dan automatisch (zou moeten) > Leffer poli 50 x 100 mm sphere tube

Wellicht iemand dan in VBA?

Peter B · 13 jun 2017

@snb
Volgens mij bied je hier geen oplossing voor het nieuwste probleem waarbij na de maataanduiding of het combinatie aantal nog een zoekvoorwaarde staat (bol, vlak, losse hevels)

Boedashake · 13 jun 2017

snb zei:
@PB

#11 over het hoofd gezien ?

Deze had ik gezien, maar dit vertaalde niet de 3de en soms 2de woord niet. Was eigenlijk gelijk doorgegaan met de code en dit zag er veelbelovend uit. Was daardoor dus te laat met reageren, sorry.
En kreeg resultaten als Leffer red steen 40 x 90 mm terwijl er vertaald was Leffer red stone en zou moeten zijn Leffer red stone 40 x 90 mm.

snb · 13 jun 2017

Dat ligt aan de inconsistente gegevens in je werkboek, niet aan de code.

Boedashake · 13 jun 2017

snb zei:
Dat ligt aan de inconsistente gegevens in je werkboek, niet aan de code.

Ik heb nog de rijen gekopieerd en geplakt en zouden nu identiek moeten zijn. Dus het nederlands veld A Blad1 is hetzelfde als veld A Blad2.
Maar krijg niet dezelfde resultaten als met de code, de code is haast de gehele oplossing. Op de uitzonderingen die ik had aangegeven na.

Ik heb het bestandje toegestuurd waarbij ik de vertaalknop heb gebruikt waarbij toch niet alle woorden vertaald lijken te worden. Ik heb al de velden gekopieerde en geplakt zodat deze identiek zijn.

Bekijk bijlage test3.xlsb

Ik hoop op een VBA oplossing, anders zal ik de uitzonderingen handmatig moeten nalopen.

Gegevens matchen

Gebruiker

Terugkerende gebruiker

Gebruiker

Inventaris

Bijlagen

Gebruiker

Gebruiker

Meubilair

Inventaris

Gebruiker

Gebruiker

Verenigingslid

Gebruiker

Gebruiker

Gebruiker

Verenigingslid

Gebruiker

Gebruiker

Gebruiker

Verenigingslid

Gebruiker

Wij waarderen jouw privacy