fonctions A+1 et "contient"

[fm1616]

Bjr à ts.

J'ai un fichier xls dont les ts éléments sont sur une seule colonne. Cette colonne contient plusieurs centaines de lignes.

Problème : j'ai besoin de "récupérer" le contenu de certaines cellules en fonction soit :
- de leur contenu (si elles contiennent "@", alors le contenu s'affiche ailleurs, si elles contiennent nº tel, alors le nº s'affiche à coté du mail)
- du contenu de la cellule précédente : si A1 commence par "F10" alors le contenu de la cellule A2 doit s'afficher à coté du mail et du nº de tél.

Je vous joins un fichier.
J'espère que vous pourrez m'aider. En attendant vos réponses, je vous remercie pour le tps que vs me consacrerez.

 

[phlaurent55]

Re : fonctions A+1 et "contient"

Bonjour FM1616,

en voyant ton fichier joint, je constate que les coordonnées de 2 établissements sont repris sur 11 lignes et qu'un établissement est repris sur 10 lignes
pour réaliser ta demande par boucle dans une macro, ce serait plus pratique d'avoir le même "formattage"
cela est-il possible ?

à+

 

[pierrejean]

Re : fonctions A+1 et "contient"

bonjour fm1616
Salut phlaurent

Vois si cela te convient

 

[job75]

Re : fonctions A+1 et "contient"

Bonsoir à tous,

Comme pierrejean, que je salue, a donné une solution VBA, voici une solution par formules matricielles.

EDITION : pour que ça fonctionne, il faut que :

- au dessus du nom de l'entreprise la référence contienne toujours "F1"

- devant le N° de téléphone il y ait toujours "Nº tel " , avec une (merci abcd ) espace intermédiaire et une à la fin

- pour chaque entreprise il y ait bien les 3 éléments recherchés, et seulement une fois.

A+

 

[fm1616]

Re : fonctions A+1 et "contient"

Bjr PierreJean et merci pour ton aide.

Je viens d'appliquer ta macro au fichier d'origine (en modifiant les paramètres de recherche) mais je suis confronté à un problème : ts les contacts n'ont pas de "Nº tel" ou "@". Je me retrouve donc avec les noms corrects des contacts dans la première colonne mais les mails et les nº de tél. ne correspondent pas.
As-tu une solution ?

 

[James007]

Re : fonctions A+1 et "contient"

Bonjour,

Il te faut utiliser la très belle formule de job75 ... qui a l'avantage d'être adaptée à la structure "dump" de ton fichier ...

A +

 

[job75]

Re : fonctions A+1 et "contient"

Bonjour à tous,

Merci James. La solution de pierrejean est aussi bonne (les critères sont les mêmes).

ts les contacts n'ont pas de "Nº tel" ou "@".

Que ce soit en VBA ou par formules, s'il n'y a pas moyen de repérer correctement les 3 champs, le problème est insoluble, il faut compléter alors la colonne A manuellement .

Edit : encore que en VBA... le problème peut se résoudre s'il n'y a effectivement pas de N° de téléphone ou d'adresse email dans une entreprise. S'il y ces champs mais sans "N° tel" ou "@", c'est difficile mais peut-être possible en créant des fonctions repérant les séries de chiffres (téléphone) ou les liens hypertexte (email).

A+

 

[James007]

Re : fonctions A+1 et "contient"

Bonjour à tous,

@ Job75

Je n'avais pas fait assez attention ... j'ai crû que tout était en modulo 10 ...
Comme ce n'est pas le cas, il faut obligatoirement passer par un point fixe pour déterminer le modulo, avant de faire l'extraction ...
Idéalement l'identifiant Fxxxx pourrait servir de point de repère ...
ou sinon l'adresse email ( sans doute plus aléatoire ... puisqu'on bosse déjà avec des gens qui n'ont pas le téléphone ... alors une adresse email !!! )

Qu'est-ce-que tu en penses..??

A +

 

[fm1616]

Re : fonctions A+1 et "contient"

Bjr et merci vs ts pour votre implication.

Je viens de prendre connaissance de vos réponses et particulièrement la dernière de Job75.
Ça semble assez compromis.
Je ne sais pas si cela peut vous aider, mais sachez que même s'il n'y a pas de nº de tél. ou de mail, les champs "Nº de tel" et "E-Mail" sont tjs présent : pour être plus clair, je propose de consulter la PJ.

Merci.

 

[pierrejean]

Re : fonctions A+1 et "contient"

Re

Veux-tu tester cette version ?
Est supposée fonctionner sous la condition stricte que toutes les societes aient bien un code commencant par F10

 

[James007]

Re : fonctions A+1 et "contient"

fm1616,

Il te faudrait tout de même vérifier l'extrait de fichier que tu as posté ...

Parce que si l'on peut comprendre que les séquences soient de onze ou dix champs à cause du nombre de lignes fluctuant de l'adresse ..., il est plus étonnant que les champs "contact", "téléphone", "pays" voient leurs positions respectives être interchangées en passant d'un record à un autre ...

Car cet extrait vient forcément d'un fichier central lui même structuré de façon cohérente ...

A +

 

[fm1616]

Re : fonctions A+1 et "contient"

Super et merci encore.
Ça fonctionne !

Une autre question : si je veux rajouter les autres champs (adresse, code postal ville), je dois modifier quelles lignes de ta VBA ?

 

[job75]

Re : fonctions A+1 et "contient"

Re, salut pierrejean ,

Un essai avec le 1er fichier de pierrejean modifié.

Le lien hypertexte est créé même s'il ne l'est pas en colonne A.

La macro :

Sub test()
Dim n As Long, ref As Range
Range("B1:D65536").ClearContents
For n = 1 To Range("A65536").End(xlUp).Row
  If Range("A" & n) Like "F1*" Then
    Set ref = Range("B65536").End(xlUp).Offset(1)
    ref = Range("A" & n + 1)
  End If
  If Range("A" & n) Like "Nº tel*" Then ref.Offset(0, 1) = Trim(Replace(Range("A" & n), "Nº tel", ""))
  If Range("A" & n) Like "E-MAIL*" Then
    ref.Offset(0, 2) = Trim(Replace(Range("A" & n), "E-MAIL", ""))
    If ref.Offset(0, 2) <> "" Then _
      ActiveSheet.Hyperlinks.Add Anchor:=ref.Offset(0, 2), Address:="mailto:" & ref.Offset(0, 2)
  End If
Next n
End Sub

EDITION : j'ai simplifié la macro et surtout ajouté "mailto:" pour l'ancrage.

A+

 

[pierrejean]

Re : fonctions A+1 et "contient"

Re

Version avec report de l'adresse et du contact
Suppose :
Que le contact soit precédé de Contact (sans respect de la casse)
Que les lignes d'adresse (avant le code postal) soient situées entre le code postal et le nom de l'etablissement et ce même s'il y en a plusieurs
Que le departement suive le code postal

 

[job75]

Re : fonctions A+1 et "contient"

Re,

Je pense que tu m'excuseras, jeanpierre, de suivre avec mes solutions, car je m'amuse aussi

Dans cette solution, la seule contrainte c'est que les adresses et le contact en colonne A soient avant le N° de téléphone. Elles se placent après la colonne Email.

Sub test()
Dim n As Long, ref As Range, refA As Range
Range("B2:K65536").ClearContents
For n = 2 To Range("A65536").End(xlUp).Row
  If Range("A" & n) Like "F1*" Then
    Set ref = Range("B65536").End(xlUp).Offset(1)
    ref = Range("A" & n + 1)
    Set refA = Range("A" & n + 2)
  End If
  If Range("A" & n) Like "Nº tel*" Then
    ref.Offset(0, 1) = Trim(Replace(Range("A" & n), "Nº tel", ""))
    Range(refA, Range("A" & n - 1)).Copy
    ref.Offset(0, 3).PasteSpecial xlPasteValues, Transpose:=True
    Application.CutCopyMode = False
  End If
  If Range("A" & n) Like "E-MAIL*" Then
    ref.Offset(0, 2) = Trim(Replace(Range("A" & n), "E-MAIL", ""))
    If ref.Offset(0, 2) <> "" Then _
      ActiveSheet.Hyperlinks.Add Anchor:=ref.Offset(0, 2), Address:="mailto:" & ref.Offset(0, 2)
  End If
Next n
End Sub

A+