Identification de cellules aux textes voisins

**David Lemarquand** · 02/05/2018, 17h22

Bonjour à toutes et tous,

Comme vous pourrez le constater via mes "stats", ceci est mon premier message sur ce forum ! Compte tenu de mes compétences en VBA (et en développement de manière général), vous comprendrez très vite que mon passage ici sera très bref.. bien que ce langage me passionne.

Afin de ne pas passer pour un opportuniste, j'ai passé les deux derniers jours à croiser les différentes infos qui pourraient m'aider dans mon problème. J'ai conscience qu'il n'y a rien de pire qu'un novice qui n'a même pas cherché dans les topics préexistants les réponses à son banal problème. Mes copiés/collés de macro chopés sur le net sans même comprendre de quoi il ressort m'ont donné l'envie de comprendre - voire mieux d'écrire ma propre macro. (Mal?)heureusement, on ne s'improvise pas développeur en 2 jours.

Voici mon objectif :

Je possède une liste d'adresses dans une première colonne (environ 30000 lignes) ainsi qu'une deuxième (environ 10000 lignes). Pour les deux colonnes, les adresses s'écrivent sous le format classique. Par ex : 14, rue Saint-Michel 14000 CAEN.
Je veux pouvoir identifier les adresses de la deuxième colonne (B) qui se glissent dans la première (A). Néanmoins, les textes ne sont pas identiques et ne se font pas face à face. Si je reprends mon exemple précédent, on pourrait retrouver dans la colonne A à la ligne 1500 le "2 r st-michel 14000 CAEN" et dans la colonne B à la ligne 500 le "2 rue saint michel 14000 Caen".
Vous remarquez donc qu'il y a des différences dans la chaîne de caractère.

J'ai pensé à géocoder les adresses via les API Google et comparer les longitudes et latitudes, toutefois dans le cadre de mon travail le résultat ne serait pas satisfaisant. Je cherche à identifier des copropriétés qui peuvent intégrer plusieurs adresses. Si je géocode le 2-8 allée des acacias, il n'y aura qu'un point correspondant au 2 allées des acacias. Dans la colonne B qui est le résultat d'adresses déclarées, les habitants ne déclarent pas l'ensemble des adresses qui composent leur copro. C'est pourquoi il est important que les cellules aux chaines de caractères similaires/voisines s'identifient afin de laisser place à un travail manuel de vérification.

J'avais trouvé cette macro :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.
    Set CompareRange = Range("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' Loop through each cell in the selection and compare it to
    ' each cell in CompareRange.
    For Each x In Selection
        For Each y In CompareRange
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

Avec un peu trop d'enthousiasme et d'optimisme, j'ai cru qu'il suffirait de rajouter une condition si x = "1, 2 ou 3 mots de y" ou encore x = "70% des caractères de y" alors on colorie la cellule active.

Je tiens à préciser que ces adresses couvrent toute la France. Aussi, il me semble que pour plus de facilité, il faudrait que j'isole par département (voire par ville) les adresses afin d'éviter les similarités entre toutes les rues récurrentes comme les "rue de la république" ou encore "rue jeanne d'arc" que l'on peut retrouver dans de nombreuses communes.

Ci-joint, une liste d'adresses si quelqu'un serait tenté de se pencher sur ce problème "difficile" selon un ami développeur ou encore ce que j'ai pu lire sur les problèmes concernant les chaînes de caractères. J'y ai intégré tous les cas possibles auxquels je pourrais faire face.

Merci beaucoup pour votre considération,

David.

**Menhir** · 02/05/2018, 17h35

Je pense que ton problème ne peut pas se résoudre avec un simple code VBA.

C'est ce qu'on appelle de la logique floue, un domaine réservé au cerveau humain ou à des algorithmes de haute volée difficilement transcriptibles en VBA. Le nombre de cas possibles est trop vaste et la frontière entre ce qui est "quasiment" identique et ce qui ne l'est pas est trop vague.

**David Lemarquand** · 02/05/2018, 17h46

Merci pour ta réponse.

Pensez-vous que le problème se simplifierait si j'enlevais le code postal et la commune ?

**Menhir** · 02/05/2018, 18h32

Je pense au contraire que le code postal et la commune sont les éléments sur lesquels ce type de recherche serait le plus "simple" à développer.
Quoi que... pour la commune, pas simple d'expliquer à VBA que "St jean des monts" et Saint-Jean-des-Monts" sont identiques.

**patricktoulon** · 02/05/2018, 18h49

re
Bonjour je rejoint un peu menhir sur ce coup la
cependant il existe des algo bazooka qui font une analise de similarité
le plus performant a ce jours a ma connaissance est celui ci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub test3()
chaine1 = "St-jean-des-monts"
chaine2 = "Saint-Jean-des-Monts"
MsgBox "chaine 1=" & chaine1 & vbCrLf & "chaine 2=" & chaine2 & vbCrLf & "resultat = " & similaire(chaine1, chaine2) & "%"
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub test4()
chaine1 = "St jean des monts"
chaine2 = "Saint-Jean-des-Monts"
MsgBox "chaine 1=" & chaine1 & vbCrLf & "chaine 2=" & chaine2 & vbCrLf & "resultat = " & similaire(chaine1, chaine2) & "%"
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub test5()
chaine1 = "St jean des monts"
chaine2 = "Saint-Jean-des-Mous"
MsgBox "chaine 1=" & chaine1 & vbCrLf & "chaine 2=" & chaine2 & vbCrLf & "resultat = " & similaire(chaine1, chaine2) & "%"
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Public Function similaire(ByVal s1 As String, ByVal s2 As String) As Double
'Calcul la similarité (de [0 à 1]) entre deux chaines d'après l'algorithme de Damerau-Levenshtein
'références : http://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance
'             http://mwh.geek.nz/2009/04/26/python-damerau-levenshtein-distance/
'             http://www-igm.univ-mlv.fr/~lecroq/seqcomp/node2.html
'Remarques  : Préparer les chaines car les comparaisons sont binaires : UCase(), Trim(),...
'Philben v1.0 - Free to Use
   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
    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

reste l'utilisateur de retenir le pourcentage valide pour accepter la similarité

**Transitoire** · 02/05/2018, 19h52

Bonsoir,
Je vais mêler ma médiocrité.
Bien qu'en apparence le plus sur, le code postal ne l'est pas réellement. En effet, la ville de Cannes par exemple de code 06400 et 06150 pour Cannes la bocca dispose en fait d'une trentaine de CP, que l'on retrouve certes rarement, mais? Nice en a minimum une centaine. Pareil pour la ville, mais je partage l'avis de menhir, ce sont vraisemblablement les critères de recherche de masse les plus sur.
Super Patrick ton intervention, je vais devoir ressortir mes Doliprane

Cordialement

Identification de cellules aux textes voisins

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager