Comparer ligne avec formule et faire ressortir les similitudes
Bonjour,
Désolé pour le titre, j'ai essayé d'être aussi précis que possible, mais pas évident.
Je joins un fichier exemple en annexe de ma demande pour plus de clarté.
Voici ce que j'ai :
- Une liste d'abonnement, avec pour chaque abonnement des informations de contact.
- Toutes les adresses ne sont pas structurées exactement de manière identique. Dans plusieurs cas, le propriétaire du contrat ne reçoit pas directement les communications, mais passe par un gestionnaire, par exemple une gérance. Dans mon fichier, "P.A." = Pour adresse.
Ce que je cherche à réaliser.
- Pouvoir trier mes informations par gérance. Comme l'info de la gérance n'est pas forcément dans la même colonne je suis embêté.
- Je voudrais donc créer une colonne qui fait ressortir la gérance. J'ai plus de 3'000 lignes, faire cela manuellement prendrait un temps certain.
J'ai donc fait une liste de toutes les gérances que nous avons dans le fichier.
-> Je cherche maintenant une formule qui va comparer chacune des lignes avec la liste des gérances et si une des gérances est trouvée sur la ligne, inscrire la gérance dans une case donnée de la ligne.
Dans mon fichier exemple, il faudrait par exemple :
Comparer la ligne 2 avec la liste dans ma feuille "liste Gérance" et écrire en J2 "Régie Dupont" car c'est une gérance de ma liste.
Je suis à disposition pour tout complément d'information et vous remercie d'avance pour le temps que vous me consacrez.
Meilleures salutations
AxelR
Bonjour AxelR,
J'ai pris un peu de temps, mais promis, j'accélère
Voici un essai avec la liste sous forme de tableau structuré et la recherche de la gérance sur les colonnes A à D. Dans cette configuration, il vaut mieux que la gérance n'apparaisse qu'une fois (sinon, il faudrait peaufiner un peu) :
=INDEX(GERANCES[Noms];EQUIV(1;NB.SI(A2:D2;"*"&GERANCES[Noms]&"*");0))en matriciel !
Cdlt,
Bonjour 3GB,
Merci beaucoup, c'est exactement ce que je cherche à faire, c'est absolument génial.
Normalement la gérance n'apparait qu'une fois par ligne, mais il se peut que deux gérances différentes apparaissent dans la même linge. C'est rare, parfois une des entité de la liste est propriétaire et sous-traite la gestion à un des autres de la liste. Dans notre exemple, cela donnerait :
(A2) Régie 22, (B2) P.A. Régie Dupont, Rte du Rhone 47, (C2) CP 12, etc.
-> Existerait-il un moyen, dans le cas ou deux noms de la liste apparaissent, de ne prendre que le deuxième ?
Merci encore pour ton aide précieuse, c'est un sacré coup de main !
Cordialement
AxelR
Oui, mais ça commence à devenir un peu lourd, il faudrait peut-être songer à une fonction personnalisée dans ce cas...
En tout cas, voici un essai (ce que j'ai trouvé de mieux pour le moment) où le renvoi dépend de l'ordre dans la liste et non de l'ordre d'apparaition dans les colonnes A à D :
=SIERREUR(INDEX(GERANCES[Noms];GRANDE.VALEUR(--(NB.SI(A2:D2;"*"&GERANCES[Noms]&"*")>0)*LIGNE(GERANCES[Noms])-LIGNE(GERANCES[#En-têtes]);2)) & " - ";"")
& SIERREUR(INDEX(GERANCES[Noms];GRANDE.VALEUR(--(NB.SI(A2:D2;"*"&GERANCES[Noms]&"*")>0)*LIGNE(GERANCES[Noms])-LIGNE(GERANCES[#En-têtes]);1));"Introuvable")Edit : à la rigueur, il serait peut-être plus simple, si c'était possible, d'identifier ces cas particuliers en les associant ou en créant une seconde liste...
Cdlt,
Super,
Merci beaucoup pour ton implication. De mon cas il me faudra plutôt faire travailler la formule d'après l'ordre d'apparition dans les colonnes. Dans les cas ou j'ai deux nom de la liste, c'est toujours le deuxième nom que je prendrais
Après, tu as raison, cela commence à devenir lourd. Je travail depuis ce matin sur mon fichier, c'est claire qu'avec la formule étendue à 3000 entrées, mon fichier commence à montrer des signe d'instabilité :).
Je vais donc utiliser ta formule pour faire ressortir les informations voulues et je ferais un copier coller des données afin d'alléger le tout. Je ferais travailler la formule uniquement pour la mise à jours des données.
Merci encore pour ton aide.
Bien à toi,
AxelR
Bonjour Tulipe_4,
Merci pour ta contribution, je vais tester les deux formules afin de voir laquelle est la plus légère en terme de "calcul" extrapolé à l'ensemble de mon fichier.
Meilleures salutations
AxelR
Bonsoir à tous,
Au cas où, le mieux que j'ai trouvé par formule pour garder l'ordre d'apparition, c'est ça :
=SUPPRESPACE(SIERREUR(INDEX(GERANCES[Noms];EQUIV(1;NB.SI(A2;"*"&GERANCES[Noms]&"*");0));"")
&" "&SIERREUR(INDEX(GERANCES[Noms];EQUIV(1;NB.SI(B2;"*"&GERANCES[Noms]&"*");0));"")
&" "&SIERREUR(INDEX(GERANCES[Noms];EQUIV(1;NB.SI(C2;"*"&GERANCES[Noms]&"*");0));"")
&" "&SIERREUR(INDEX(GERANCES[Noms];EQUIV(1;NB.SI(D2;"*"&GERANCES[Noms]&"*");0));""))Sinon, on part vraiment sur de l'usine à charbon...
Et voici quand même un essai avec une fonction personnalisée :
Function Gerance(Plage, Liste) As String
Dim reg As Object, dico As Object
If Liste.Count > 1 Then motif = Join(Application.Transpose(Liste), "|") Else motif = Liste
If Plage.Count > 1 Then chaine = Join(Application.Transpose(Application.Transpose(Plage)), " ") Else chaine = Plage
Set reg = CreateObject("vbscript.regexp")
Set dico = CreateObject("Scripting.dictionary")
With reg
.Global = True
.ignorecase = True
.Pattern = motif
If .test(chaine) Then
For i = 0 To .Execute(chaine).Count - 1
dico(.Execute(chaine)(i).Value) = ""
Next i
End If
End With
Gerance = Join(dico.keys, " - ")
End FunctionElle marche avec la configuration que nous avons vu pour l'instant...
Bonne soirée,
Bonjour,
Un grand merci pour ces solutions alternatives.
J'ai juste un blocage pour la macro. Quand je veux la lancer il me demande un nom de la macro.
C'est surement une histoire toute simple, mais je ne suis pas trop habitué au VBA.
En vous souhaitant une excellente semaine.
AxelR
Bonjour AxelR,
C'est normal, ce n'est pas une procédure mais une fonction. Elle peut donc s'utiliser dans le code ou sur feuille afin de renvoyer une valeur. Tu devrais voir le fichier joint, la fonction est saisie comme une fonction normale et renvoie la ou les gérances...
Il est possible de faire une procédure mais la fonction me parait plus adaptée dans ce cas...
Cdlt,
Bonjour,
Merci bien pour l'information, je ne connaissais pas les fonctions, je vais potasser sur le sujet du coup.
Meilleures salutations.
AxelR