Fichier "plat" : Aligner données et les déconcaténer

Bonsoir le forum et bon week-end !

Je suis nouveau ici et je n'ai pas beaucoup de connaissances en VBA, mais je pense que c'est le seul moyen par lequel je pourrais résoudre mon problème, si ce n'est pas le cas et que vous pensez pouvoir m'aider sans passer par VBA, ça serait super !

Je m'explique : je souhaite constituer un fichier "plat" à partir de plusieurs feuilles Excel. Chacune des feuilles Excel comporte plusieurs données concernant une clé commune. Par plat, je veux dire constituer une feuille Excel avec une ligne par clé commune qui répertorie toutes les informations récupérées dans les différentes feuilles Excel.

Exemples de feuilles Excel :

Feuille 1 -> Nom de la personne ; Sexe ; Age

Feuille 2 -> Nom de la personne ; Adresse(s) ; Taille

Feuille 3 -> Nom de la personne ; Couleur préférée

Feuille 4 -> Nom de la personne ... et ainsi de suite.

Comme vous pouvez le constater, il y a une clé commune entre ces fichiers : le Nom de la personne. Cependant chacun de ces fichiers comporte des données concernant cette personne que je souhaiterais donc mettre sur une seule ligne par Nom. Par exemple, si la personne a plusieurs adresses, je souhaiterais les répartir sur autant de colonnes que nécessaire en les distinguant, si possible, par Adresse (1), Adresse (2) suivi de la Taille par exemple.

Avant de faire appel à une aide extérieure, j'ai essayé une méthode avec mes modestes connaissances en Excel :

- J'ai concaténé les informations de chaque feuille avec comme critère ";" pour les déconcaténer par la suite.

- J'ai ensuite fait un deuxième concaténage de toutes les adresses possibles pour un client avec comme critère "^" entre chaque adresse unique pour pouvoir les déconcaténer par la suite.

- J'ai introduit ces données concaténées dans un fichier principal à l'aide d'une fonction recherchev en prenant comme critère le Nom de la personne, clé commune entre tous les fichiers.

Cependant, je n'arrive pas à déconcaténer correctement le résultat. En effet lorsque je déconcaténe à partir du critère "^", j'ai bien autant de colonnes qu'il y a par exemples de données dans la Feuille 2 pour un Nom unique, mais lorsque je veux déconcaténer à partir du critère ";" la première colonne écrase les autres valeurs.

Je pense que la solution serait de faire un code VBA qui demande à créer X colonnes entre chaque valeur concaténée extraite de la Feuille 2 et de déconcaténer automatiquement chacune de ces valeurs à partir du critère ";".

Pourriez-vous m'aider s'il vous plaît ? Si je n'ai pas été assez explicite dans mon problème, merci de me le préciser.

Bonne soirée à vous et bon week-end !

BOnjour

Avec 2016, très simple à faire avec PowerQuery intégré, mais poste un exemple anonymisé correspondant à 10 personnes

Bonjour Chris et merci pour ta réponse !

N'ayant malheureusement pas mon fichier à disposition, j'en ai crée un modèle sous une version antérieure d'Excel, j'espère qu'il t'aidera à mieux cerner mon problème. Ceci est donc un exemple, j'ai en réalité un volume de données plus important.

Est-il possible de créer, à partir des feuilles 1 et 2, un fichier plat directement avec Power Query sans passer par mes manipulations de concaténage ? (afin de diminuer au possible les phases de "bidouillage" de données)

Je ne connais pas les fonctionnalités de Power Query, mais si le problème peut être résolu avec ces fonctionnalités, pourrais-tu m'expliquer comment faire s'il te plaît ?

Merci et bonne soirée

Bonjour

Exemple avec les deux onglets

Le fait de juxtaposer en colonnes plusieurs adresses et tailles complique par rapport à une simple correspondance mais comme tu vois c'est faisable

Merci pour l'exemple Chris

Effectivement c'est le fait d'avoir plusieurs lignes dans certaines feuilles pour une même clé unique qui me pose problème pour constituer le fichier plat.

Cependant je t'avoue ne pas avoir compris comment tu as fait le tableau dans la feuille "Modèle souhaité". Pourrais-tu m'expliquer pas à pas ce que tu as fait afin que je puisse le refaire avec plusieurs feuilles s'il te plaît ?

Bon dimanche

Bonjour

Voici les étapes une fois mis chaque plage sous forme de tableau structuré

On se place dans une cellule du Tableau1 : Données, à partir d'un tableau, ce qui ouvre PowerQuery

Sortir par Fermer et charger Dans, connexion seulement

Refaire avec, Tableau3, Tableau4 (je vois que j'ai nommé Tableau4 et Tableau5 mais pas très grave)

Pour Tableau2 on reste dans PowerQuery :

• onglet Ajouter une colonne, Index
• onglet Accueil, Nouvelle source, Autres sources, Requête vide
• dans la barre de formule taper =Tableau2 puis renommer la requête en Tableau2b
• Transformer, Regrouper par, Nom et Compter les lignes
• Ajouter une colonne, colonne personnalisée, formule : List.Numbers([Nombre],[Nombre],-1)
• cliquer sur la double flèche près du titre List
• Trier par Nom et Personnalisé
• supprimer la colonne Nombre
• Ajouter une colonne, Index
• Accueil, fusionner les requêtes, choisir en seconde requête Tableau2, sélectionner la colonne Index de chaque requête, jointure Interne
• cliquer sur la double flèche près du titre Tableau2 : tout décocher sauf Adresse et Taille
• onglet Accueil, Nouvelle source, Autres sources, Requête vide
• dans la barre de formule taper =Tableau2b puis renommer la requête en Tableau2c
• Ajouter une colonne, colonne personnalisée, formule : Number.ToText([Personnalisé],"00")&" Adresse"
• supprimer les colonnes Index et Taille
• renommer la colonne Adresse Info
• onglet Accueil, Nouvelle source, Autres sources, Requête vide
• dans la barre de formule taper =Tableau2b puis renommer la requête en Tableau2d
• Ajouter une colonne, colonne personnalisée, formule : Number.ToText([Personnalisé],"00")&" Taille"
• supprimer les colonnes Index et Adresse
• renommer la colonne TailleInfo
• Accueil, Ajouter des requêtes : choisir Tableau2c
• Trier par Nom et Personnalisé.1
• sélectionner la colonne Personnalisé.1, Transformer,Colonne de Tableau dynamique, sélectionner Info, ne pas agréger
  reprendre la requête Tableau1 :
• Accueil, fusionner les requêtes, choisir en seconde requête Tableau2d, sélectionner la colonne Index de chaque requête, jointure Interne
• cliquer sur la double flèche près du titre Tableau2d : cocher une colonne puis remplacer tout ce qui est dans la ligne de formule par = Table.ExpandTableColumn(PreviouStep, "Tableau2d",List.Union(List.Transform(PreviouStep[Tableau2d],each List.Select(Table.ColumnNames(Tableau2d), each not Text.Contains(_,"Nom")))))
• trier par Nom
• Accueil, fusionner les requêtes[/b], choisir en seconde requête Tableau4, sélectionner la colonne Personne de chaque requête, jointure Interne
• cliquer sur la double flèche près du titre Tableau4 et sélectionner Couleur
• répéter ces 2 manip pour Tableau5 en adaptant le nom du champ
• sortir par Fermer et charger dans, Tableau et choisir l'emplacement

Re

Merci beaucoup pour la solution Chris. J'essaie de tester ça ce midi et je te fais un retour rapidement.

Bonne journée à toi

Bonjour Chris,

Encore merci pour avoir détaillé pas à pas la démarche, d'autant plus que je me rends compte que ça a l'air assez complexe. Il va falloir que je me penche sur le fonctionnement de cet outil une fois mon fichier constitué.

Je suis ton raisonnement pas à pas, et je me suis retrouvé bloqué à une étape après la création du Tableau2b, après avoir crée la colonne personnalisée

lorsque tu écris :

"•cliquer sur la double flèche près du titre List".

Je précise que dans mon fichier réel, la clé commune entre les fichiers est "Numéro" et non le "Nom" que j'avais pris sur le coup dans mon exemple.

Peut-être ai-je raté quelque chose, mais :

- je ne vois pas de titre List à part les valeurs de la colonne Personnalisé

- la seule double flèche que je vois, c'est celle de l'en-tête de la colonne Personnalisée et elle me propose deux choix (voir capture ci-dessous).

Je pense donc que ce n'est pas de cette flèche dont tu veux parler ? Dans ce cas, pourrais-tu m'indiquer de quelle double flèche tu veux parler ?

De même, je n'ai pas compris comment ensuite trier par Numéro et Personnalisé.

Je vais continuer sans cliquer sur la double flèche ni sans trier par Numéro et Personnalisé en attendant.

Bonne journée !

RE

Oui c'est un faute de frappe : c'est Personnalisé (remplie du mot List doù l'erreur à cette heure tardive...)

Pour le tri j'ai donné les noms des colonnes dans l'ordre où doit se faire le tri : c'est comme dans Excel on trie en déroulant la flèche déroulante près du titre de colonne

Bonsoir Chris,

En suivant pas à pas ta démarche, je me rends compte qu'elle est parfaite pour un nombre limité de colonnes, mais qu'avec le volume de données que j'ai (7 Feuilles en tout avec 20+ colonnes par feuille, et si je comprends bien la méthode, il faut faire la démarche avec la formule Number.ToText([Personnalisé],"00")&" Taille" et la suite pour chacune des colonnes que je souhaite agréger), j'ai peur que ce soit une tâche interminable malheureusement avec la fluidité que j'ai pour l'instant sur ce nouvel outil pour moi …

Je vais continuer encore un peu, mais sinon je passerais le sujet en résolu, car c'est déjà une super méthode pour un nombre limité de colonnes

Bonjour

Le problème n'est pas nombre de colonnes, c'est le fait de vouloir juxtaposer n colonnes de même type, ce qui est en dehors des normes base de données, d'autant plus que le nombre est variable, normes que PowerQuery respecte

Bonjour Chris,

C'est vrai que c'est très nouveau pour moi.

Je reviendrai à ton mode d'emploi dans une semaine car je pense qu'il faudra que je réalise ce fichier malgré tout, d'ici là je te remercie pour avoir pris le temps de m'aider et te souhaite un bon Week-end !

Je passe le sujet en résolu donc, encore merci !