XL 2019 Rechercher un libellé approximatif dans un tableau et afficher une valeur

[Adexa]

Bonjour

Je sollicite votre aide pour automatiser mon fichier, car ma formule ne fonctionne pas (colonne A).

Pour explication : Je reçois des données de prélèvement (colonne C) de plusieurs lignes (j'ai mis les 7 premières lignes)
Je souhaite, à côté de chaque ligne, afficher le code comptable dans la colonne A, comme dans l'exemple (colonne B) qui a été fait à la main.
Pour afficher ce code, je fais une recherche du libellé de la colonne C dans le tableau à côté colonne J.

Je ne sais pas si je suis très claire dans mes explications.

Je pense que le problème vient du fait que mes libellés ne font pas identiques.

Pouvez-vous m'aider ?

Merci beaucoup

 

[Dudu2]

Bonjour,

La formule de l'espace de @TooFatBoy fonctionne bien mais pas à 100% (exemple pour "PRELT Cabinet MML" stocké en table "MML CABINET", il n'y a pas de résultat.

Alors peut-être en Power Query, encore faut-il savoir exécuter la requête et où sont les tableaux de référence et de résultat. Perso je n'y connais rien et je ne sais même pas exploiter le fichier de @mromain.

Alors je pencherais pour soit une macro soit une fonction personnalisée en VBA avec ou sans l'algorithme de Levenshtein auquel fait référence @patrictoulon (STP donne le lien, je n'ai pas trouvé ta fonction).

Plutôt une macro dans un fichier dédié avec les codes de référence des clients facturés (411*) qui s'appliquera à une liste contenue dans un autre fichier car je suppose que ces listes varient à chaque "arrivage".

 

[TooFatBoy]

La formule de l'espace de @TooFatBoy fonctionne bien mais pas à 100% (exemple pour "PRELT Cabinet MML" stocké en table "MML CABINET", il n'y a pas de résultat.

Effectivement. Et j'ai remarqué qu'elle ne marche pas non plus avec "ML KABINNÉ".
Désolé mais j'ai pô trouvé mieux dans l'espace proche.

 

[patricktoulon]

re
tiens j'ai ajouter une colonne
donc
en colonne A la formule te donne la ligne correspondante a l'id de la colonne B dans le tableau 2
et en colonne D la formule donne la ligne correspondnate au texte de la colonne C dans le tableau 2
en C1 tu tape le pourcentage acceptable (sans symbole)
ca donne ceci
on vois déjà qu'a 30% c'est assez cohérent

@Dudu2 https://fr.wikipedia.org/wiki/Distance_de_Levenshtein
la fonction est dans le classeur
sinon dans le moteur de recherche de exceldownloads tu le trouve en deux deux en cherchant "similaire" avec pseudo patricktoulon
ici par exemple

Microsoft 365 - Nombre de caractères uniques communs entre 2 cellules

Bonjour à toutes et tous Je dispose de 2 bases : 1 où on trouve un référentiel de noms de chevaux (Base B : 1500 noms) et une base (Base A : 1800 lignes) où a été saisi le nom et d'autres données, sans recours à des listes de validation ...résultat : la liste saisie comporte beaucoup d'erreurs...

excel-downloads.com

voila

 

[Phil69970]

Re

La formule de l'espace de @TooFatBoy fonctionne bien mais pas à 100% (exemple pour "PRELT Cabinet MML" stocké en table "MML CABINET", il n'y a pas de résultat.

C'est normal car les mots sont inversés

2) Inversion nom prénom

 

[patricktoulon]

re
avec ma similaire ca passe a la première passe unordered l'occurence est 100%

 

[Dudu2]

Que de propositions... @Adexa va s'y perdre, surtout que je rajoute la mienne.

Le fichier TrouveComptesClients.xlsm contient la macro et le tableau de référence des comptes clients.
Le fichier ESSAI.xlsx est le fichier de test qui ne contient que les données à traiter par la macro.

Fichiers... voir post suivant !

 

[Dudu2]

J'ai ajouté des couleurs:

Il devient donc possible de filtrer par couleur pour vérifier ou corriger les comptes.

Mon algorithme ne match pas:
- ARF75 et ARF 75
- TRANS et TRANSPORT
Ça pourrait s'améliorer en comparaison secondaire sur le regroupement de mots quand l'un en a moins et sur les premiers caractères du plus court.
C'est possible mais un peu risqué pour l'affectation des comptes sur des centaines ou milliers de lignes. Mieux vaut vérifier, sinon la compta ...

Attention aussi aux possibilités multiples (qui sortent en orange dans ma liste)
Le libellé PRELT BELLA SASU peut tout aussi bien convenir:
- à 411BELLA BELLA
et
- à 411TRIO TRIO SASU
SASU est un acronyme (Société par actions simplifiée unipersonnelle) mais le code ne le sait pas (à moins de le sophistiquer exagérément) et ,dans le doute, mieux vaut une intervention humaine de vérification.

 

[Dudu2]

Sur demande, je peux coder pour matcher:
- ARF75 et ARF 75
- TRANS et TRANSPORT
Sachant que les comparaisons exactes des mots sont dominantes et que ces comparaisons secondaires devront toujours être vérifiées car non sûres. Et c'est plus complexe d'un point de vue algorithmique car il faut associer les comparaisons secondaires aux comparaisons exactes existantes pour prendre les meilleures, et si non existantes prendre les comparaisons secondaires existantes.

 

[patricktoulon]

Bonjour @Dudu2

Sachant que les comparaisons exactes des mots sont dominantes et que ces comparaisons secondaires devront toujours être vérifiées car non sûres. Et c'est plus complexe d'un point de vue algorithmique car il faut associer les comparaisons secondaires aux comparaisons exactes existantes pour prendre les meilleures, et si non existantes prendre les comparaisons secondaires existantes.

ARF75 et ARF 75 ; ma fonction se charge de ça
pour TRANS et TRANSPORT c'est une erreur de vouloir faire ça
ca pourrait être TRANSMISSION ,TRANSITOIRE , TRANSPOSE etc,etc...

ce qui n'a pas de sens tu en conviendra surtout que tu va alourdir encore la fonction

ma fonction se charge déjà de
mot dans l'ordre ex: Jennifer RAMMAN ->> RAMMAN JENNIFER -->100%
faute dans les mots: Jennifer RAMMAN ->> RAMMAN JENIFER-->Jenifer RAMMAN-->70/80%
mot manquant : les pommes du pays de galle--> les pomme pays de galle-->70/80%
les mots en plus : les pommes vertes du pays de galle--> les pommes du pays de galle-->70/80%

tout ça est analysé en binaire en calculant la distance en supprimant, inversant,ajoutant,remplaçant un caractère(caractère par caractère )
comme c'est en binaire c'est bien plus rapide

 

[Dudu2]

Salut @patricktoulon,
Ça a l'air puissant comme fonction qui reconnait les fautes dans les mots.
Dans ta fonction, si on a "TOTO ARF 75 TATA" tu reconnais ARF75 ou juste quand il y a seulement "ARF 75" ?

 

[patricktoulon]

il reconnait les deux a un pourcentage différent
et c'est a toi de décider a quel pourcentage c'est acceptable
la fonction ne donne que la distance en pourcentage de 0 à 100

et oui c'est très puissant
cela dit rien n'est bible
a partir du moment ou par exemple je vais tester "ARF 75" et "ARF75" et "ORF 75" ou encore "ARF 74"
il risque d'avoir des pourcentage egaux

c'est pour ca que j'ai rajouté en 2021 la partie unordered
le principe est simple
2 chaines
on les coupe toute les deux par les espaces(peut être avec des replace avant pour les "-,etc..)
on prend le plus petit array
et on teste le like dans l'autre chaine
si il y a tout ben on est à 100% on passe pas par le binaire
cela dit aussi on risque de taper à coté car si par exemple j'ai
jean paul tourniquet des vents SAS 20500 oka

et qu'en chaine 2 j'ai
jean paul tourniquet des vents
jean paul tourniquet des vents SAS abcdef
jean paul tourniquet des vents trucmuche

la partie unordered dans ma fonction va prendre la plus petite chaine comme la 1
donc jean paul tourniquet des vents
et les tests donneront la première occurrence des autres chaines (plus grande) contenant la chaine1
on est donc tributaire aussi de l'ordre des chaines testées en cas d'égalité


mais cette partie (unordered) fera une différence discriminatoire et surtout éliminatoire
entre par exemple : "maison" et "maisons" ou encore avec une faute de frappe "moison"
tandis qu'en binaire par l'étape substitution du caractère x de la chaine 1 et celui de la chaine 2
ou l'a suppression , et bien si ça colle il est sauvé avec une perte de points certes mais c'est moindre que l'élimination pour une faute de frappe ou une erreur de pluriel

 

[Dudu2]

Ouais, ton truc est sophistiqué !
De mon coté, avec quelques difficultés, j'ai ajouté le traitement des mots séparés en 2 mots maximum pour par exemple "ARF 75" = "ARF75" et réciproquement. Une telle égalité reste colorée en jaune (=> à vérifier).
Les tables, y compris des mots concaténés pour la comparaison des mots séparés, sont préparées au début pour éviter des appels de fonction VBA au moment des tests de comparaison.

 

[patricktoulon]

re
c'est peut être sophistiqué mais c'est surtout plus juste
je t'invite si tu veux y faire un tour dans la discussion que j'ai ouverte avec fichier et code
afin de ne pas polluer cette discussion
qui sait a deux on va peut être trouver un hybride

 

[Dudu2]

"Plus juste", je ne sais pas. Plus flexible et généraliste sans aucun doute.
Mais pour la comparaison ici des libellés d'opérations et des intitulés de comptes, un % de distance ou d'exactitude n'est peut-être pas l'idéal. A l'auteur de la question de voir...

 

[Dudu2]

Bonjour,
@Adexa, tu t'en sors ?