Microsoft 365 Crée un VBA pour repérer des doublons sur un tableur excel

[patrick965]

bonjour cher communauté
voila j'aimerais faire ceci sur mon fichier excel
toute en concervant les fonctionalitée présente au tableau déja présentent
peu on faire en sorte que excel soie tres précis pour repérée les doubons
la première vérification de doublon dans la colonne Q (total HT) . il y aura de bonne chance de trouver des montant similaire .
ensuite
il va ensuite ce référée au autre colonne E (Fournisseurs)
ensuite
la coonne C (Brève description des achats) qui va changer dans le prochain tableau pour (bon de travail ou numéro de facture)
ensuite il va finir sa vérification sur la colonne B (date)
et au final déterminer un doublon même si le nom est pas exactement pareil partout .

il faudrait par exemple que Excel ne puisse identifier un doublons de 2 catégorie différant comme par exemple achat et temps travailler même si 2 montants en Q son identique

et

peu on faire en sorte d'avoir en plus de la surbrillance un message d'alerte quand il y a un doublon
exemple ; (attention nous détectons 2 ligne similaire soie la ligne 120 et 360 voulez vous les conserver ou supprimer la dernière entrée )...
donc a ce moment l'utilisateur peu vérifier et faire son choix en conséquence.
si on conserve le doublons la surbrillance s'enlève

parce que je trouve super les surbrillance mais reste ensuite a démeler et trouver quel ligne va avec quel ligne doublon.

le message d'alerte lui va etre interessant surtout l'ors de nouvelle entrée

si tu a des idée plus simple et ou meilleur je suis ouvert je croie que tu comprend mon message

merci pour votre temps merci beaucoup a tous pour votre aide

 

[gbinforme]

peu on faire en sorte d'avoir un message d'alerte quand il y a un doublon
exemple ; (attention nous détectons 2 ligne similaire soie la ligne 120 et 360 voulez vous les conserver ou supprimer la dernière entrée )...

Je t'ai rajouté une procédure "doubles" qui doit le faire. Elle se lance lors des modifications.

 

[patrick965]

merci Gbinforme
merci pour ton temps
j'ais tester le tout et la seul chose que j'aimerais voir si on peu faire quelque chose ...
ces
les message d'alerte en cas de doublons apparais sa ces super .
la chose qui est moins pratique ces ...
lorsque le message est a l'écran ;exemple double ligne 9 et ligne 235 . nous ne somme pas en messure d'aller voir ce qui ce trouve a ces ligne. comment peu t'on faire en sorte de pouvoir aller voir les ligne a lien avec l'alerte... les ligne peuve elle apparaitre en entier au message pour pouvoir les comparée et même apporter la modification directement dans la case de l'alerte ... ou encore les mettre en surbrillance etc
a tu une petite idée qui serais pratico pratique...

et peu on avoir ces alerte a l'ouverture du document Excel

merci bk

 

[gbinforme]

Bonsoir
Pour pouvoir comparer tes doubles, je t'affiche les 2 lignes et tu peux supprimer l'une ou l'autre ou aucune.
Le format d'affichage est identique à celui du tableau mais comme je n'ai pas les formats canadiens c'est assez curieux.
La seconde est en surbrillance et tu peux naviguer dans la page.
Regarde si cela te convient.

 

[patrick965]

salut gbinforme
super beau travail
dans la rechercher de doublons peu on faire en sorte de pousser l'intelligence de Excel que celui ci repère les doublons même s'il sont pas identique mais avec de minime variante comme par exemple si dans description on inscrit patrick,adams, sébastien ou adams,sébastien,patrick ou encore s'il y a 0.01$ de différance entre 2 ligne similaire il verra pareil les doublons sans aucune faille.

merci bk super beau travail

​

 

[gbinforme]

Bonjour,
Tolérer 0.01$ de différence c'est possible mais celui qui a saisi cela, l'a sans doute fait volontairement.
Prendre les noms dans n'importe quel ordre c'est une usine à gaz car il va y avoir les espaces à tenir compte aussi et les virgules présentes ou non, etc
L'intelligence de Excel n'a pas d'art divinatoire encore et même si j'ai utilisé "like" au lieu "=" cela ne résout pas le problème. Que fait-on si la saisie d'une lettre est doublée dans un mot ? Il faut un humain pour le détecter et encore pas sûr car l'on devine souvent ce qui devrait être écrit sans voir l'erreur.
Merci de ton commentaire et ce ne fut pas si simple d'afficher les messages en cascades.

 

[gbinforme]

Bonjour,
Finalement même si Excel ne peux pas être considéré pour intelligent, j'ai créé une fonction qui réalise ce que tu souhaites pour les textes. Par contre pour ta tolérance de 0.01$ je t'ai mis une valeur paramètre dans le code que tu peux modifier.

 

[patrick965]

gbinforme​

ces pas Excel qui est intelligent ces vous ​

vous faite du beau travail vraiment j'apprécie
avec cette dernière version il devrais pas y avoir 2 ligne pareil ou très similaire qui au moins doit capter l'attention de l'humain pour etre sur de ne par faire payé au client 2 x les même cout.
la question que je me pose est si on abandonne l'alertes d'un prétendue doublons , a la réouverture du fichier , Excel demandera t'il a chaque foie.

je me demandais aussi si je change un nom au scellule 8 ou encore j'ajoute une cellule en cliquant sur total présentement en 238 un de cest ajout ou changement de nom va t'elle affecter les contrôle automatique du fichier que tu a implanter. si oui comment peu on faire en sorte que ca ne les affecte pas si un tel ajout ou changement est produit.

dernière chose hors contexte mais depuis plusieurs temps j'essaie de faire en sorte de mettre une option (supprimer les donner le la page)
mais je n'y arrive pas je veux faire en sorte que l'lorsque on clique sur le bouton un message d'alerte demande aite vous sur de vouloir supprimer les donner de la page ... et en appuillant sur oui on aurais l'option d'enregistrer une copie avant la suppressions.
ensuite toute les donner inscrit dans le tableau redevienne vierge prête pour une autre projet.
je veux que aucune fonction du tableau soie affecter juste les inscription du tableau et le nom du client etc ...


je sais pas si cet quelque chose que tu serais capable de m'aider

merci beaucoup pour ton temps passer sur mon tableur

 

[gbinforme]

re

la question que je me pose est si on abandonne l'alertes d'un prétendue doublons, a la réouverture du fichier , Excel demandera t'il a chaque foie.

Effectivement et pour valider la ligne il faudrait rajouter un caractère particulier dans une rubrique, par exemple "?" ou "." pour pouvoir l'ignorer ensuite.

supprimer les donner le la page

Je te regardes cela en début de semaine.

 

[patricktoulon]

bonjour
@gbinforme
tu pourrait intégrer ma fonction similaire pour analyser les phrase dans un ordre différent ou avec des caractères diférent tels que les accentués
la fonction renvoie de 1 à 100 qui est le pourcentage de similarité

Public Function similaire(ByVal s1 As String, ByVal s2 As String) As Double
    Const cFacteur As Long = &H100&, cMaxLen As Long = 256&   'Longueur maxi autorisée des chaines analysées
    Dim l1 As Long, l2 As Long, c1 As Long, c2 As Long
    Dim r() As Integer, rp() As Integer, rpp() As Integer, i As Integer, j As Integer
    Dim c As Integer, x As Integer, y As Integer, z As Integer, f1 As Integer, f2 As Integer
    Dim dls As String, ac1() As Byte, ac2() As Byte
    Dim px As Double, p As Double, oz As Long
Dim t1, t2
'*****************************************************
'analyse dans un ordre different
  If s1 = s2 Then similaire = 100: Exit Function
    
    t1 = Split(Replace(s1, "-", " "), " "):    t2 = Split(Replace(s1, "-", " "), " ")
    If UBound(t2) > UBound(t1) Then tbl = t12: s2 = s1 Else tbl = t1
    If IsArray(tbl) Then
        If UBound(tbl) > 1 Then
            p = 100 / UBound(tbl) + 1
            For oz = 0 To UBound(tbl): px = px + IIf(s2 Like "*" & tbl(oz) & "*", p, -p): Next
            If px = 100 Then similaire = 100: Exit Function
        End If
    End If
    '**************************************************
  'analyse binaire
  l1 = Len(s1): l2 = Len(s2)
    If l1 > 0 And l1 <= cMaxLen And l2 > 0 And l2 <= cMaxLen Then
        ac1 = s1: ac2 = s2   'conversion des chaines en tableaux de bytes
        'Initialise la ligne précédente (rp) de la matrice
        ReDim rp(0 To l2)
        For i = 0 To l2: rp(i) = i: Next i
        For i = 1 To l1
            'Initialise la ligne courante de la matrice
            ReDim r(0 To l2): r(0) = i
            'Calcul le CharCode du caractère courant de la chaine
            f1 = (i - 1) * 2: c1 = ac1(f1 + 1) * cFacteur + ac1(f1)
            For j = 1 To l2
                f2 = (j - 1) * 2: c2 = ac2(f2 + 1) * cFacteur + ac2(f2)
                c = -(c1 <> c2)   'Cout : True = -1 => c = 1
                'suppression, insertion, substitution
                x = rp(j) + 1: y = r(j - 1) + 1: z = rp(j - 1) + c
                If x < y Then
                    If x < z Then r(j) = x Else r(j) = z
                Else
                    If y < z Then r(j) = y Else r(j) = z
                End If
                'transposition
                If i > 1 And j > 1 And c = 1 Then
                    If c1 = ac2(f2 - 1) * cFacteur + ac2(f2 - 2) And c2 = ac1(f1 - 1) * cFacteur + ac1(f1 - 2) Then
                        If r(j) > rpp(j - 2) + c Then r(j) = rpp(j - 2) + c
                    End If
                End If
            Next j
            'Reculer d'un niveau la ligne précédente (rp) et courante (r)
            rpp = rp: rp = r
        Next i
        'Calcul la similarité via la distance entre les chaines r(l2)
        If l1 >= l2 Then dls = 1 - r(l2) / l1 Else dls = 1 - r(l2) / l2
    ElseIf l1 > cMaxLen Or l2 > cMaxLen Then
        dls = -1   'indique un dépassement de longueur de chaine
    ElseIf l1 = 0 And l2 = 0 Then
        dls = 1   'cas particulier
    End If
    similaire = dls * 100
End Function

'exemple
Sub test()
MsgBox similaire("patricktoulon", "patrick toulon") & "%"
MsgBox similaire("patricktoulon", "patricktoulouse") & "%"
MsgBox similaire("jean-yve dupont", "dupont jean-yve") & "%"
End Sub

en formule c'est par exemple
=simailaire(A1,A10]
donc A1 est comparé a A10
2méthode sont intégrées dans cette fonction
1 les mots dans l'ordre et le désordre
2 analyse binaire mesurant la distance (algorithme de levenshtein)

L'algorithme de Levenshtein (1966) recherche la distance de deux chaînes C1 et C2 en évaluant le coût minimal d'édition en termes d'ajout, de suppression et de substitution de caractères.

 

[patrick965]

Bonjour gbinforme et patricktoulon​

patricktoulon je ne serais pas ou et comment intégrée ceci a mon tableau. je ne sais par si la recherche sera encore plus précise que ce qui est déja fait mais je veux simplement diminuer au maximum les doublons d'une page même si il on pas été inscrit exactement pareil .

j'aime beaucoup le travail fait a ce jour merci beaucoup .
et outre le bouton ou fonction supprimer je me demandais aussi si je change un nom au cellule 8 ou encore j'ajoute une cellule en cliquant sur total présentement en 238 un de ces ajout ou changement de nom va t'elle affecter les contrôle automatique du fichier que tu a implanter. si oui comment peu on faire en sorte que ca ne les affecte pas si un tel ajout ou changement est produit.

 

[gbinforme]

Bonjour à tous
@patricktoulon merci pour le code que tu as développé je vais voir si cela peut améliorer.

J'avais modifié sans regarder le poste et en attendant je t'envoie le classeur présent :
- je t'ai rajouté la possibilité de valider un double
- je t'ai mis la fonction de sauvegarde et/ou de réinitialisation
Regarde si cela te convient

 

[patrick965]

salut gbinforme​

oui ca me semble très bien je me demande juste si il est possible d'ajouter valider la ligne la première identifier comme il est mentionner pour la 2 iem ligne . et peu ton modifier les titre en cellule 8 sans affecter le fonctionnement parce que en i9 au lieu de reliquat HT je conserverais total HT
et en C8 je changerais le titre pour Bon de travail ou numéro de facture
donc a l'avenir ce sera que des numéro et des lettre dans cette section.
et a la initialisation il y a juste ceci qui ne s'efface pas
en N,O,P,Q 16 a 18

Acompte reçu 1 cheque recu 4 juillet 2023	90 423.32 $
Acompte reçu 2 cheque 5957 recu 24 juillet 2023	37 662.24 $
Acompte reçu 3 cheque 6019 le 04 aout 2023	110 650.49 $
Montant dû	(238 736.05) $

et le nom du chantier et client ne s'efface pas non plus
en B,C 2 et 3

de plus j'ais fait un teste et j'ais réinitialiser le tableau et j'ais inscrit 2 nouvelle entrée

	01-sept-23	patrick,adams	Batiment Tricolor					- $	2	6	0.5	18	23.00	70.00 $	1 610.00 $	1 610.00 $
	03-sept-23	adams,patrick	Batiment Tricolor					- $	2	6	0.5	18	23.00	70.00 $	1 610.00 $	1 610.00 $

mais j'ais eu aucune alerte de doublon...

il est tu possible d'avoie les alerte en temps réel a la fin d'une entrée un peu comme je les tester

merci beaucoup

 

[patrick965]

Bonjour à tous
@patricktoulon merci pour le code que tu as développé je vais voir si cela peut améliorer.

J'avais modifié sans regarder le poste et en attendant je t'envoie le classeur présent :
- je t'ai rajouté la possibilité de valider un double
- je t'ai mis la fonction de sauvegarde et/ou de réinitialisation
Regarde si cela te convient

merci donner moi des nouvelle si ca rend encore plus optimum la recherche

 

[gbinforme]

Bonsoir,

peu ton modifier les titre en cellule 8 sans affecter le fonctionnement

sans aucun souci il ne sont pas utilisés : seul le nom de l'onglet et celui du tableau sont mis dans le code.

il y a juste ceci qui ne s'efface pas

Cela ne fait pas partie du tableau mais je l'ai effacé sauf le "Montant dû" qui a une formule et pourra être réutilisé

mais j'ais eu aucune alerte de doublon...

normal la date est différente !

il est tu possible d'avoie les alerte en temps réel a la fin d'une entrée

Cela ne me parait pas souhaitable car la saisie de chaque champ sera très rallongée de façon conséquente car tout le code va s'exécuter dans 99% des cas pour rien .

J'ai testé le code de @patricktoulon mais si le code est beaucoup plus scientifique sans doute, lorsque tu inverse 2 mots comme nom/prénom,il donne une similitude de 6% et comme tu souhaites les considérer comme doubles cela ne fonctionne pas. Ce code n'est pas adapté à ta problématique même si je le garde précieusement pour d'autres utilisations plus classiques.