Faire correspondre automatiquement Code Postal et Code Commune INSEE

[s.lafitte]

Bonjour à tous,

Après d'infructueuses recherches sur les différents fils de discussions, je me décide à vous poser ma question :

Serait-il possible de faire que "en automatique" lorsque la colonne B contient un code postal donné le code commune (INSEE) correspondant s'affiche automatiquement dans une colonne C.
Ma problématique est double, je dois pouvoir envisager l'ensemble des communes du Languedoc Roussillon soit plus de 1600 communes et en plus je dois pouvoir intégrer une option qui reconnait le nom de la commune car certains code postaux correspondent à plusieurs code commune.

Le soucis est que je dois traiter des bases de données qui comportent plus de 8000 lignes que je dois régulièrement extraire d'une autre application...

Sans cette automatisation...je passe des heures à traiter ces fichiers à la main...je sais écrie des formules simples de somme conditionnelles ou de valeur si..mais là j'avoue que cela dépasse ma maigre maîtrise....



merci beaucoup pour vos éclairages

bien cordialement

Sophie

 

[s.lafitte]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Bonjour,

Alors dans l'ordre :

@Stapple1600 : Non je n'ai jamais utilisé la fonction RECHERCHEV et je vais donc si j'en crois ton conseil éviter de m'y pencher, et m'intéresser à INDEX et EQUIV (desolée @Modeste geedee je ne connaissais pas non plus), merci pour la formule, je vais essayer de comprendre et d'utiliser

@ Gardien de phare : sur la conformité entre le nom de la commune de ma base et le fichier INSEE : Malheureusement non, la base de donnée est issue d'une vieille application métier pour laquelle les champs de saisie étaient libres tu peux donc avoir plusieurs orthographes pour la même ville... je dois pouvoir extraire ma base patients régulièrement et faire les analyses moi même (carte etc..) et pour cela je dois absolument disposer des codes INSEE notamment parce que je dois créer des zones d'intervention "normées".

Je nettoie le fichier à la main et je fais correspondre les code Insee mais c'est très long, trop long....automatiser la correspondance CP+Nom =CInsee, me permettrait de nettoyer mon fichier en 2 secondes avec mon logiciel de cartographie.

enfin voilà, je vous transmets un extrait de fichier et je reviens vers vous après avoir testé la proposition de Stapple.

merci encore

bien cordialement

Sophie

 

[Si...]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

salut

Tu aurais eu cette réponse depuis longtemps, Si... tu avais joint ton fichier avec 2 ou 3 lignes dans le premier onglet et surtout la liste des communes et codes correspondants !
Clique, bouton droit, sur le nom de l'onglet "base patient" puis Visualiser le code --> ouverture de l'éditeur VB.
Copie dans la fenêtre à droite

Private Sub Worksheet_Change(ByVal R As Range)
  If R.Column <> 5 Then Exit Sub
  With Feuil2
    l = .Columns(1).Find(R).Row
    Cells(R.Row, 3) = .Cells(l, 4)
    Cells(R.Row, 4) = .Cells(l, 2)
  End With
End Sub

A chaque saisie, si hors colonne E (5) : rien
sinon cherche dans l'autre feuille la commune et écrit les codes dans la feuille actuelle.

 

[s.lafitte]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Bonjour

Merci beaucoup Si....pour ce code qui fonctionne effectivement très bien lorsque je saisie une nouvelle ligne à la main....je le garde d'ailleurs précieusement car cela peut me servir..cependant, mon fichier est extrait avec les 9000 et quelques lignes déjà remplie, j'espérais donc pouvoir insérer une formule qui face abracadabra....lol

merci et bien cordialement

Sophie

 

[Si...]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

re

Si tu veux tester et compléter toutes les lignes, tu peux utiliser une macro plus générale :

Private Sub macro()
  For Each C In Range("E2:E" & Cells(Rows.Count, 5).End(xlUp).Row)
    With Feuil2
      l = .Columns(1).Find(C).Row
      Cells(C.Row, 3) = .Cells(l, 4)
      Cells(C.Row, 4) = .Cells(l, 2)
    End With
  Next
End Sub

Une formule (plutôt 2) à tirer sur plus de 9000 lignes pourquoi pas (mais cela risque de ralentir les actions futures surtout si tu en as d'autres).

=RECHERCHEV(E2;Codes;4) en C2
=RECHERCHEV(E2;Codes;2) en D2

où Codes est le nom donné à la plage triée ='listing commune'!$A$2:$D$2278

 

[s.lafitte]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Bonjour Si...

Alors ça c'est extra génial, sauf que cela ne fonctionne que sur 3 lignes?? sniff
j'essaie de lire ta macro mais c'est dur pour moi de comprendre ce qui doit être modifié...parce que déjà c'est dur pour moi de déchiffrer ce que as écris...

Pour la formule, elle fonctionne, cependant elle se réfère à la première lettre du mot..du coup la correspondance n'est pas bonne

^^je sais, je sais, mais j'essaie d'apprendre pas de vous faire bosser et moi hop...tout roule ..donc si tu pouvais un peu m'expliquer ce serait cool
merci

bien cordialement

Sophie

 

[s.lafitte]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Re

EDITION: Voici donc une photo du petit couple
=INDEX(bdcc;EQUIV(C1;bdcp;0);2)

ou bdcc est une zone nommée qui contient les cp et les cc (sur deux colonnes)
et bdcp les codes postaux sur 1 colonne

Bonjour Stapple,

Je te remercie (et indirectement Modeste Gededee) pour m'avoir fait découvrir ces deux fonctions INDEX et EQUIV...je suis bien arrivée à les faire fonctionner et également à comprendre leur intérêt. Les combiner était plutôt malin puisque cela permet de à la fois rechercher où se trouve la valeur qui m’intéresse et la récupérer, cependant, deux bémols majeurs :

-cela sous entend que mes valeurs sont toujours rangées de la même façon car je dois donner une "position précise à EQUIV" ou alors je ne sais pas faire...ce qui méconnait que dans mon listing de patients je peux avoir dix fois voir vingt fois la même ville...
-d'autre part je n'arrive à faire fonctionner cette formule que si l'ensemble des éléments sont sur la même feuille de calcul, je ne sais pas et ne comprends pas pourquoi...sniffffff.....

enfin voilà, merci en tout cas..j'apprends des choses et cela me plait beaucoup, même si pour l'instant je n'ai toujours pas trouvé de solution à mon problème....

bien cordialement

Sophie

 

[Si...]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

salut

hé oui, dans le monde médical, on est tous « patient(e)s » !

Non seulement un code postal peut être le même pour plusieurs communes mais un nom de commune peut se retrouver dans plusieurs départements (voir Canet par exemple).
Pour avoir le code de la commune, il faut 2 renseignements !

Voici une autre macro qui tient compte de cela, à écrire dans le module de la feuil1 (« base patient ») :

Sub CC()
  Dim R As Range, C As Range
  'pour chaque cellule de la ligne 2 à la dernière non vide de la colonne "E" de la feuille de travail
  For Each C In Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    'indication pour la feuille de recherche
    With Feuil2
      'pour chaque cellule de la ligne 2 à la dernière non vide de la colonne "A" de cette feuille
      For Each R In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        'trouve le nom de commume et son code postal
        If C = R And C.Offset(, -1) = R.Offset(, 1) Then C.Offset(, -2) = R.Offset(, 3)
      Next
    End With 'mention obligatoire après un With
  Next
End Sub

 

[david84]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Bonsoir à tous,
une formule matricielle à tester :

=INDEX('listing commune'!$D$2:$D$2278;EQUIV('base patient'!D2&'base patient'!E2;'listing commune'!$B$2:$B$2278&'listing commune'!$A$2:$A$2278;0))

.
Les #NA correspondent aux communes dont l'orthographe de "base patient" n'est pas la même que dans listing commune.
A+

 

[s.lafitte]

RESOLU : Faire correspondre automatiquement Code Postal et Code Commune INSEE

salut

hé oui, dans le monde médical, on est tous « patient(e)s » !

Non seulement un code postal peut être le même pour plusieurs communes mais un nom de commune peut se retrouver dans plusieurs départements (voir Canet par exemple).
Pour avoir le code de la commune, il faut 2 renseignements !

Voici une autre macro qui tient compte de cela, à écrire dans le module de la feuil1 (« base patient ») :

Sub CC()
  Dim R As Range, C As Range
  'pour chaque cellule de la ligne 2 à la dernière non vide de la colonne "E" de la feuille de travail
  For Each C In Range("E2:E" & Cells(Rows.Count, "E").End(xlUp).Row)
    'indication pour la feuille de recherche
    With Feuil2
      'pour chaque cellule de la ligne 2 à la dernière non vide de la colonne "A" de cette feuille
      For Each R In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
        'trouve le nom de commume et son code postal
        If C = R And C.Offset(, -1) = R.Offset(, 1) Then C.Offset(, -2) = R.Offset(, 3)
      Next
    End With 'mention obligatoire après un With
  Next
End Sub

Bonjour Si....Merci Beaucoup pour ce code , je crois que tu es devenu mon héros

David, je te remercie mais cependant cette formule ne fonctionne pas...je l'avais déjà essayée...

bien cordialement

Sophie

 

[david84]

Re : Faire correspondre automatiquement Code Postal et Code Commune INSEE

Re

David, je te remercie mais cependant cette formule ne fonctionne pas...je l'avais déjà essayée...

Si elle fonctionne...à condition de la valider en matriciel (touches Ctrl,Maj et entrée).
Encore faut-il que les noms soient orthographiés de la même manière.
A+