help, vert zoeken

[qtje]

Goedemorgen,

Voor mijn werkgever ben ik verschillende goederencodes aan het corrigeren naar de juiste code.
Ik dacht dat dit in Excel goed te doen was. Nu heb ik een sheet waarin ik met vert. zoeken wel de dichtstbijzijnde waarde vind maar hij pakt telkens een waarde eronder. Is er hier een oplossing voor? Bekijk bijlage UITTESTEN.xlsx

Zie de bijlage het bestand met in de groene kolom de juiste waarde.

Hoor graag van jullie.

 

[AlexCEL]

Kun je bijvoorbeeld in kolom E handmatig een paar codes zetten die volgens jou het meest "dichtstbijzijnd" zijn?

Ik kan er nog geen logica in ontdekken.

 

[Joske2000]

Moet de formule in C niet de volgende zijn ?

=VERT.ZOEKEN(A2;A:B;2;ONWAAR)

Ik volg wel Alex dat er geen logica in zit.

Gr,

Joske

 

[AlexCEL]

Joske, volgens mij heeft jouw formule altijd B2 als uitkomst, tenzij A1 gelijk is aan A2.

 

[Joske2000]

Alex,

Wat betreft de op te zoeken code (KOLOM A) is dit inderdaad altijd de eerste code dei gevonden wordt, maar voor de rest zit er ook geen logica in tussen de toegestuurde codes en de op te zoeken codes, dus ik volg jouw verhaal wel dat er een paar handmatige codes zouden ingevuld moeten worden om een logica te vinden.

Gr,

Joske

 

[AlexCEL]

Beetje logica c.q. nadere toelichting is wel handig om een excel-formule te bedenken.

We wachten de reactie van TS af.

 

[qtje]

Sorry,

Hierbij een toelichting:

De eerste kolom zijn de toegestuurde codes die wij van de klant ontvangen.
De tweede kolom zijn de juiste goederencodes zoals we die willen hebben.
Kolom 3 heb ik een controle van gemaakt die laat zien welke codes er overeen komen maar dit zijn er heel weinig.
Kolom 4 moet de dichtstbijzijnde waarde aangeven maar bij voorbeeld 1 geeft de formule aan dat de toegestuurde code 8482.10.5048
code 8481.20.9090 moet zijn. Dit klopt niet omdat de dichtstbijzijnde code 8482.10.9090 zou zijn.
Hij verandert dus het vierde cijfer dat klopt niet.

Hoop dat dit duidelijker voor jullie is?

 

[qtje]

Ter informatie De codes komen uit Amerika en moeten dus gecorrigeerd worden naar Europese codes.

 

[qtje]

Bekijk bijlage UITTESTEN.xlsx

 

[qtje]

Nu in kolom E bij de eerste de codes die volgens ons goed zijn.

 

[plongske]

Dan zullen op zijn minst een lijst moeten hebben met de unieke Amerikaanse codes en een lijst met de overeenstemmende unieke Europese codes
Het kan toch niet de bedoeling zijn dat voor verschillende Amerikaanse code dezelfde Europese codes gebruikt worden

 

[Joske2000]

Dit maakt het voor mij niet duidelijker.
De code die je opgeeft (8482.10.9990) komt zelfs niet voor in de tabel.
Hoe wil je dan dat Excel deze ergens terugvind ?

Gr,

Joske

 

[Haije]

Wat mij betreft is het er nog niet veel duidelijker op geworden.

Maar volgens mij komt het doordat de exacte waarde niet gevonden wordt; dan neemt Excel niet de dichtstbijzijnde, maar de voorgaande (dus "kleinere") waarde

 

[qtje]

Europese codes kunnen wel gebruikt worden voor Amerikaanse. Code 8482.10.9090 staat wel in kolom B.

 

[qtje]

Kan dan niet gezocht worden naar de code die het dichtste bij de gestuurde code komt?

 

[AlexCEL]

Hierbij een mogelijke oplossing (zie bijlage).

Bekijk bijlage UITTESTEN (AC).xlsx

Gebruikte formule:

=INDEX($B$1:$B$72;SOM(ALS(ABS(SUBSTITUEREN($A2;".";"")-SUBSTITUEREN($B$2:$B$72;".";""))=MIN(ABS(SUBSTITUEREN($A2;".";"")-SUBSTITUEREN($B$2:$B$72;".";"")));RIJ($B$2:$B$72);0)))

Opmerkingen:
1. Dit is een matrix-formule, dus dient afgesloten te worden met Control+Shift+Enter.
2. Vereist is dat de lijst met "juiste codes" enkel uit unieke waarden bestaat. Selecteer daartoe de lijst, en haal duplicaten weg met "Duplicaten verwijderen" in menu "gegevens".
3. Als je lijst met unieke waarden langer wordt dan moet je het bereik uiteraard aanpassen...

 

[qtje]

Dankjewel voor jullie hulp! Ik denk dat deze formule wel klopt Alex!
Wat een snelle reacties ook super! Snap nog niet hoe je het voor elkaar heb gekregen maar daar ga ik nog is naar kijken.

 

[AlexCEL]

Kleine uitleg:
1. Middels de SUBSTITUEREN functies worden de codes omgezet in getallen (de punten worden uit de code gehaald)
2. De getallen worden van elkaar afgetrokken en de absolute waarde (ABS) genomen zodat er geen onderscheid is tussen een negatief of positief verschil. Hieruit volgt een lijst (omdat de formule ingevoerd is als matrix-functie).
3. Uit deze lijst wordt het minimum (MIN) gezocht.
4. Het verschil tussen de "toegestuurde code" en de "juiste code" moet nu vergeleken worden met dit minimum per rij (matrix-functie). Bij overeenkomst volgt het RIJnummer, anders waarde 0. Hieruit ontstaat een lijst die eruit ziet als "...,0,0,0,0,rijnummer,0,0,0,0,..."
5. De elementen uit de lijst worden opgeteld (SOM): uitkomst = ... + 0 + 0 + rijnummer + 0 + ... = rijnummer.
6. Middels de INDEX-functie wordt in kolom B het berekende rijnummer opgezocht en de bijbehorende waarde getoond.

Hieruit blijkt ook waarom de "juiste codes" slechts eenmaal mogen voorkomen. Anders wordt de lijst bij stap 4 bijvoorbeeld "...,0,0,0,rijnummer 1, rijnummer 2,...". Als ik dit bij elkaar optel komt er een veel te groot getal uit en werkt de INDEX functie niet naar wens.