XL pour MAC Retraitement (trop complexe pour moi) d'une base de données

[didcac]

Bonjour à tous,
Je suis bloqué par mon incompétence...
Ayant une base de données EXCEL pour MAC plutôt mal organisée, je souhaite retraiter certaines informations de type "texte". Je précise que si c'est aussi curieusement agencé, ce que c'est un export de FILEMAKER, à l'origine, qui a éclaté les données sur diverses colonnes.
J'ai joint un fichier EXCEL (très partiel car il y a en réalité 4.000 lignes / fiches !) afin que vous puissiez voir comment procéder. Les colonnes susceptibles d'accueillir les nouvelles données retraitées pourraient commencer à la colonne EA, EB, EC, ED, etc, jusqu'à l'infini ou presque... Je les ai renommées exprès (et dans le bon ordre logique des choses).
D'une manière générale, j'avais défini auparavant une méthode de saisie UNIFORMISÉE qui va probablement aider dans ce retraitement.
En gros, la voici :
• une personne est toujours explicitée par Mr ou Mme ou Mlle (puis un espace) puis son prénom (si connu) (puis un espace) puis nom de famille.
• son téléphone est une suite de minimum 8 chiffres (souvent 10) car il y a parfois des numéro anciens sans préfixe.
• les adresses mail, c'est classiquement un bloc de lettres autour de @ (avant y'a souvent le prénom-nom collé ou réuni par un underscore) et après une société se terminant par .com ou .fr (en général).

Il y a beaucoup de colonnes en tout, mais celles que je veux rectifier sont les suivantes (avec description du contexte et une idée de méthodologie selon moi) :

• Colonne P : Une cellule correspond à un très gros champ TEXTE de FILEMAKER. Ici, il y a des titre-prénom-nom, puis parfois une Fonction et/ou Service (toujours entre parenthèses), puis un ou deux téléphones, parfois un e-mail mais cela se termine TOUJOURS par un signe / (SLASH) avant de passer à une seconde personne qui a à peu près le même schéma, et ainsi de suite avec parfois 12 ou 16 personnes.
En fait, mon objectif est de créer diverses nouvelles colonnes qui récupèrent ces différentes données de manière à individualiser ces gens dans une fiche. Ainsi, chacun aura un téléphone unique et un e-mail personnel, si je veux leur envoyer un document nominativement, par exemple.
J'imagine qu'il faut commencer par recopier dans une colonne TEXTE les données globales de chaque individu en indiquant dans la Macro de s'arrêter au caractère Espace qui précède chaque SLASH de fin (signe que l'on passe ensuite à une nouvelle personne, sauf à la TOUTE FIN où il n'y en a en principe pas, normalement).
Voici un exemple concret du Fichier que je vais vous commenter :
En regardant par exemple la Cellule P18 (choisie au hasard) : il y a 5 personnes listées dans cette cellule Texte, dont il va falloir répartir les diverses données dans les cellules allant de la plage EA à WT (c'est déjà nommé et pré-structuré). Dans d'autres cellules de cette même colonne, cela va parfois jusqu'à 14 ou 16 personnes, voire parfois 1 seule (très aléatoire).

Je souhaiterais donc , si possible, voir apparaître les données dispatchées suivantes :
Colonne EA8 : Titre (civilité)
Colonne EB8 : Prénom
Colonne EC8 : Nom
Colonne ED8 : Service (ou Fonction) (sans copier les parenthèses)
Colonne EE8 : Téléphone 1
Colonne EF8 : Téléphone 2 éventuel
Colonne EG8 : Téléphone 3 éventuel
Colonne EH8 : Courriel
(jusqu'à 16 personnes, jusqu'à WT18)
et ainsi de suite pour la deuxième personne, puis troisième, etc.

• Colonne Q : C'est exactement pareil que Colonne P (des individus à reconfigurer sur le même schéma)
Colonne EI8 : Titre (civilité)
Colonne EJ8 : Prénom
Colonne EK8 : Nom
Colonne EL8 : Service (ou Fonction) (sans copier les parenthèses)
Colonne EM8 : Téléphone 1
Colonne EN8 : Téléphone 2 éventuel
Colonne EO8 : Téléphone 3 éventuel
Colonne EP8 : Courriel
(jusqu'à 16 personnes jusqu'à XB18)

• Colonne R : là, il faut dispatcher les deux types de données (adresse de sites internet et de courriels qui ont des structures bien caractéristiques) sur 2 colonnes (XD et XE)

• Colonne DZ : là des mots sont entre guillemets, et (selon le nombre de mots présent) il faut les dispatcher sur 1, 2 ou 3 colonnes (XF, XG et XH) sans réécrire les guillemets. Le premier mot dans XF, le deuxième éventuel en XG, le troisième éventuel en XH.


J'ignore si une seule Macro sera capable de tout gérer ainsi ???
La Macro devra être écrite pour avoir la taille de gérer jusqu"à 4.000 lignes (car la base de données est plus grosse qu'ici).

NB : dans l'immédiat, les colonnes P et Q ne sont pas à vider ou virer, car par commodité je me réserve la possibilité de les réunir dans un seul champ dénommé HISTORIQUE (archives globales) qui me permettra de balayer du regard un grand nombre d'informations sans perdre du temps à cliquer dans des cellules individuelles. Ces dernières seront en revanche utiles pour segmenter les actions de communication individuelles comme je l'ai déjà dit.

J'ai conscience que le résultat ne sera probablement pas parfait au sens où, même si j'ai essayé depuis fort longtemps de respecter un protocole de saisie homogène, on n'est jamais exempt d'erreur de frappe ou d'oubli divers qui remettent alors en question mon idée de méthode, mais si c'est exploitable à 80 %, ce serait déjà très bien.

Je sais qu'il y a ici des personnes hyper compétentes avec EXCEL, alors que moi-même n'ai que quelques rudiments très insuffisants. Merci de bien vouloir essayer de m'apporter les solutions que vous verriez de par votre grande expérience.
A vous lire.
Merci d'avance.

 

[eriiic]

Bonjour,

1) à mettre dans un module standard :

Function extraireMAJ(ch As String, Optional maxi As Long = 999)
    Dim tmp, i As Long, j As Long
    tmp = Split(ch, " ")
    For i = 0 To UBound(tmp)
        If UCase(tmp(i)) <> tmp(i) Or j > maxi Then tmp(i) = vbNullString: j = j + 1
    Next i
    extraireMAJ = Application.Trim(Join(tmp, " "))
End Function

Syntaxe sur feuille :
tous les mots en majuscule :

=extraireMAJ(A5)

les 2 premiers :

=extraireMAJ(A5;2)

2) faut pas exagérer. Pas de fonction personnalisée pour concaténer 3 cellules...
eric

 

[didcac]

Merci bien.

1) J'ai copié ta formule, mais cela ne génère rien (je joins le document en xlsm). On m'a dit qu'une ligne est incorrecte "à l'extérieur d'une procédure".
Après j'ai mis un Sub Macro et End Sub, mais idem, rien n'est lancé.

NB : dans la formule, y a-t-il l'effacement de l'éventuelle virgule accolée au NOM, (au cas où) ?
NB2 : la version "syntaxe sur feuille" avec définition d'un nombre ne marche pas bien : quand je mets 2 NOMS en MAJUSCULES il n'en retourne qu'un seul, quand j'en mets 5 pour voir les 3 premiers là aussi il n'en retourne qu'un seul. Curieux...
En revanche, quand c'est pour tous les mots en MAJUSCULES, là ça marche. C'était juste une remarque, car je préfère la Macro.


2) Là, j'avais en effet déjà fait une tentative avec le verbe CONCATENER comme fonction, mais il me renvoyait un texte tout agrégé sans Espaces entre les mots.
Par exemple : Soirée du PersonnelFRANCE TELECOM1998

Et comment, de surcroît, insérer des parenthèses ou guillemets encadrant par exemple le deuxième mot (toujours avec un espace entre chaque mot) ?
Exemple : Soirée du Personnel (FRANCE TELECOM) 1998


Merci.

 

[eriiic]

1)
NB : dans la formule, y a-t-il l'effacement de l'éventuelle virgule accolée au NOM, (au cas où) ?
J'ai fait ce qui était demandé, donc non

Function extraireMAJ(ch As String, Optional maxi As Long = 999)
    Dim tmp, i As Long, j As Long
    tmp = Split(Application.Trim(Replace(ch, ",", " ")), " ")
    For i = 0 To UBound(tmp)
        If UCase(tmp(i)) <> tmp(i) Or j > maxi Then
            tmp(i) = vbNullString
        Else
            j = j + 1
            If j = maxi Then ReDim Preserve tmp(0 To i): Exit For
        End If
    Next i
    extraireMAJ = Application.Trim(Join(tmp, " "))
End Function

2) heuuu, comment dire... On touche le fond là non ?
Si tu veux te servir d'excel il faudrait peut-être faire l'effort de connaitre les bases.
Ce n'est pas le but de ces forums que de te tenir la main.
Cherche des sites d'initiation pour apprendre les rudiments (oui, les rudiments, moins que le B.A.BA)
eric

 

[didcac]

Bonjour Eric,

1) J'ai essayé de coller la Fonction que tu indiques, et j'obtiens cela :

Et quand j'exécute la Macro, rien ne se passe.

Donc, comme ça ne marchait pas, j'ai tenté ça :

Et en la lançant, rien ne se passe non plus.
Ne faut-il pas compléter dans Sub l'étendue de l'action (plage) ?
Serait-ce un souci avec le MAC ?
Que faire ? Si tu envoies ta formule déjà collée dans une feuille EXCEL de ton PC, ça ne pourrait pas mieux marcher ?
Merci.


2) Là, je tiens à préciser une chose :
Il y a dix jours, en suivant pas à pas un Tutoriel pour faire cela, j'ai utilisé la Fonction de concaténation CONCAT() ainsi qu'il le préconisait. Or, je viens de comprendre en refaisant de nouvelles recherches que c'est une autre syntaxe de Fonction pour le MAC, ce qui explique que j'avais eu droit à un message d'erreur (alors que j'avais parfaitement compris et saisi les bonnes données !).
Aussi, en corrigeant simplement la syntaxe, j'ai parfaitement réussi (et les espaces sont désormais gérés). Je n'y étais donc pour rien...


Merci néanmoins de ton aide, bien sûr !

 

[eriiic]

Bonjour,

Syntaxe sur feuille :
tous les mots en majuscule :
=extraireMAJ(A5)
les 2 premiers :
=extraireMAJ(A5;2)

 

[didcac]

Bonjour Eric,
Finalement, oui, je vais prendre cette méthode de Syntaxe sur feuille qui est plus simple et fonctionne.
Ce qui est étrange, c'est que ça marche sur certaines Feuilles et pas sur d'autres (cela renvoie alors un message d'erreur et ne génère rien), alors que c'est rempli à l'identique (voire copié-collé), dans les mêmes numéros de cellules, par cohérence...
En outre, les feuilles où ça marche, il n'y a plus de problème de virgule à la fin. C'est très bien.


Sinon, je suis en train de finaliser la BASE RÉELLE, et bien que les retraitements soient globalement bons, j'ai un dernier bug général au lancement de la Macro qui bloque tout.
Mais après une dizaine d'heures de Testing, j'ai enfin exactement trouvé la source de tous ces récents et derniers soucis. Cela concerne finalement traiteQ "BO".
En fin de compte, c'est simplement dû au fait qu'environ 600 fiches ont une syntaxe de début dans la Colonne BO visiblement perturbante (Année directement écrite, au lieu d'avoir un ou plusieurs mots avant la DATE), et parfois également après d'autres virgules séparatrices (quand il y a plusieurs DATES consécutives dans un même Lieu de réception).
Or, j'ai besoin, pour mes concaténations futures, de disposer de la dénomination complète de chaque événement (retraités par traiteQ dans les 5 cases dédiées).

Après m'être penché dessus un long moment, il me semble que cela peut être résolu à travers 2 actions (à déclencher nécessairement AVANT traiteQ "BO") selon les 2 seuls cas avérés :

1) l'insertion d'un TEXTE + ESPACE (déjà présent dans chaque fiche, en Cellule T) avant une DATE "isolée"
Exemple > 94 : THEATRE DE PARIS

2) la copie, puis insertion, d'un INTITULÉ de réception + ESPACE déjà présent dans la Cellule BO avant des DATES suivantes, avec aussi parallèlement la copie des DEUX POINTS + LIEU de réception après ces mêmes DATES suivantes (c'est comme une sorte d'"encadrement" de chacune de ces dates pour qu'ensuite traiteQ repositionne tout cela à la bonne place).
Exemple > Soirée du Personnel 2004, 2005, 2009 : LA COUPOLE

Pour illustrer plus simplement cela, j'ai joint un fichier appelé "1 traitement" qui explique tout avec des exemples. Cela tient sur 6 lignes, en étant résumé.
Mais j'ai aussi développé cela plus en détails, ensuite, au cas où tu voudrais des précisions. J'ai essayé d'être le plus complet possible.

Et il y a aussi en pièce jointe le dernier Fichier complet sur lequel j'ai rempli les cellules T et BO des mêmes valeurs (pour le Testing).

Si tu peux avoir l'amabilité de voir cela, la toute dernière pierre à l'édifice avant le grand lancement !

Après cela, il n'y aura plus d'obstacles à la création de la Base officielle. Car j'ai en plus vérifié que traiteQ gérait parfaitement les virgules à la fin des Noms parfois plantogènes. C'est bien le cas.

Encore merci pour tout !!

 

[eriiic]

Bonjour,

ça fait 5 fois que j'entend ça. 2 possibilités :
- tu attends le prochain confinement car là j'ai d'autres choses à faire
- ou tu démarres un nouveau sujet pour exposer ton problème
eric

 

[didcac]

Bonjour Eric,
Oui, je sais bien, mais c'est la première fois que "ce n'était pas prévu".
J'étais bien obligé d'attendre de voir le comportement d'une Macro pour pouvoir m'apercevoir qu'il y avait quelque chose qui (éventuellement) clochait (et bloquait tout) derrière.

Je regrette bien évidemment ce coup d'arrêt aussi près du but, mais je comprends très bien.
D'ailleurs, je t'avais même suggéré de ne faire que la phase n°1 en n'insérant que les Intitulés de réception aux bons endroits, et en t'indiquant que je pensais avoir trouvé une solution pour la phase n°2 de récupération du Lieu d'organisation, comme indiqué sur la Feuille d'explication.
A défaut d'un nouveau confinement souhaitable pour personne, si tu penses que la Phase n°1 n'est pas trop compliquée et que tu trouves les minutes pour le faire, ce sera évidemment avec plaisir (et soulagement !).
NB : j'ai pu convertir hier la totalité des dates de BO en format AAAA (j'ignore si c'était un motif de difficulté accrue).

Sinon, et quoi qu'il en soit, je te remercie pour tout le superbe travail que tu as fait. Il y avait peu de personnes aussi compétentes et patientes que toi, selon toute vraisemblance, et ce fut une belle expérience humaine.
Navré de t'avoir parfois déçu par ma relative incompétence. J'ai cependant appris diverses choses que je réutiliserai par la suite.

Très cordialement.
Didier

 

[eriiic]

Un conseil : démarre ta (tes ?) nouvelle question toute neuve si tu veux finir ton projet.
Moi j'ai fini, j'ai fait au moins 40 fois plus que ce que je voulais au départ.
eric

 

[Jam]

Hello le fil,

Bravo à Eriiic pour son gros taf sur ce sujet.
J'ai une petite question car je ne connais pas la version Mac d'Excel. Possède-t-elle PowerQuery (en tant qu'addon ou intégré (dans le menu Données > Récupérer et Transformer) ? C'est l'outil parfait pour effectuer ce type de traitements de façon très simple et automatisable. Ou à défaut dégrossir de façon très significative la bdd.
Jam

 

[soan]

Bonjour Jam,

ces 2 liens pourront peut-être t'aider :

Lien n° 1 ; Lien n° 2

soan

 

[Jam]

Merci soan.

Je connais bien l'utilisation du soft Je voulais surtout savoir si cela était dispo sur Excel Mac (merci, avec les lien tu confirmes). Après c'était aussi pour suggérer la chose à didcac

Jam

 

[eriiic]

Bonjour,
didcac est passé à une autre étape : https://www.excel-downloads.com/threads/duplication-de-lignes-et-copie.20057175/page-2#post-20430597
eric

 

[didcac]

Bonsoir Eric,
Quelle bonne surprise de te revoir ! Je confirme ton superbe travail qui va bientôt trouver son épilogue, comme tu l'as vu. Cela m'a pris un temps considérable, car il a fallu entre-temps sélectionner un SGBD et surtout apprendre à le programmer !
Encore merci pour ton aide.

 

[didcac]

Bonsoir Jam,
Merci pour cette aimable suggestion, mais ce traitement est terminé (à ce niveau). J'ai vu cette fonctionnalité, d'abord sur PC, mais je n'ai pas la version 2019 pour MAC. Un autre contributeur talentueux, chris, m'a aidé avec PowerQuery, justement, depuis son PC...
Bonne soirée.