XL 2016 VBA Transposer avec conditions

[arake]

Bonjour à tous,

Pour commencer, un grand merci pour votre aide car je débute à peine dans le vba

Alors voilà, j'ai un extract de questionnaire que je cherche à structurer différemment pour en faire une base de données "clean".
Pour chaque ID (de 1 à 9), je voudrais que les groupes de colonnes encadrées en gras (dans l'onglet "ref") soient transposés en lignes, si ces colonnes sont renseignées.

Exemple:
"ID 4" a acheté la marque de bière NUMBER ONE en 3 formats différents, donc 3 groupes de colonnes : G à L , M à R, S à X .
Comment faire pour que ces 3 groupes soient présentés sur 3 lignes différentes plutôt que sur 1 seule ligne ?
Dans l'onglet "transposer", j'ai fait la manip manuellement pour vous donner un exemple.

Encore un grand merci.

 

[Hasco]

Bonjour,

Voici une solution sans macro par Power Query (Onglet 'Données/à partir d'un tableau ou d'une plage')
Pour les lignes "Aucune", je n'ai pas répercuté sur toute les colonnes.

Peut-être qu'un autre adepte de power query tel que @chris ou @merinos jettera un coup d'œil pour quelque amélioration.

Cordialement

 

[chris]

Bonjour

Peut-être qu'un autre adepte de power query tel que @chris ou @merinos jettera un coup d'œil pour quelque amélioration.

Très fort la fonction qui opère sur la table imbriquée : cela optimise vraiment

Chapeau !

(J'arrive au même résultat mais avec une foultitude d'étapes)

 

[arake]

Bonjour Roblochon,

Merci pour ta réponse.
Je n'arrive pas à ouvrir l'onglet des requêtes pour voir les étapes. Est-ce qu'il y a une manip en particulier à faire ?

 

[mapomme]

Bonjour à tous,

Un version VBA. Le code est dans module1. Cliquer sur le bouton Hop! de la feuille "Résultats".

 

[Hasco]

Je n'arrive pas à ouvrir l'onglet des requêtes pour voir les étapes

Bonjour,
Bonjour @chris , Bonjour @JHA

Cliquez dans une cellule du tableau de résultats puis dans l'onglet requête qui s'affiche sur le ruban, le bouton 'Modifier' à gauche.

Ou onglet 'Données/Requêtes et connexion', click-droit sur le nom de la requête dans le panneau lateral et 'Modifier.

@chris , il reste à afficher 'Aucune' dans le reste des lignes où catégorie = "Aucune". Une idée ?
@JHA

 

[chris]

Bonjour à tous

@chris , il reste à afficher 'Aucune' dans le reste des lignes où catégorie = "Aucune". Une idée ?

Rien à part sélectionner les colonnes MARQUE à LIEU et remplacer les valeurs null par "Aucune"...

 

[arake]

Bonjour à tous,

Un version VBA. Le code est dans module1. Cliquer sur le bouton Hop! de la feuille "Résultats".

Merci mapomme!
Si le tableau de référence contient plus de colonnes, quelles lignes dois-je modifier dans le code?
Merci encore

 

[Hasco]

Re,

En cherchant à remplacer les valeurs "null" par "Aucune", uniquement pour les lignes dont la CATEGORIE est "Aucune" je me suis rendu compte que les noms de colonnes avaient une espace traînante à la fin.
C'est corrigé dans la requête suivante du classeur ci-joint.
Les valeurs "Aucune" sont également ajoutées.

Cordialement

 

[merinos]

Bonjour

Très fort la fonction qui opère sur la table imbriquée : cela optimise vraiment

Chapeau !

(J'arrive au même résultat mais avec une foultitude d'étapes)

Je viens de decouvrir...
Très efficasse...

Rien a redire...

 

[arake]

Bonjour,
Bonjour @chris , Bonjour @JHA

Cliquez dans une cellule du tableau de résultats puis dans l'onglet requête qui s'affiche sur le ruban, le bouton 'Modifier' à gauche.

Ou onglet 'Données/Requêtes et connexion', click-droit sur le nom de la requête dans le panneau lateral et 'Modifier.

@chris , il reste à afficher 'Aucune' dans le reste des lignes où catégorie = "Aucune". Une idée ?
@JHA

Bonjour Roblochon,

Merci pour ta réponse.
Il y avait des problèmes de compatibilité et des blocages de contenus externes.

Malheureusement, il y a d'autres problèmes de compatibilité ou erreurs d'expression (?) qui s'affichent et je ne peux pas visualiser dans l'éditeur de requête ce que les formules que tu as saisies font.

L'erreur est signalée pour l'étape "Type ligne ajouté" :

Et sur d'autres étapes, il y a ce message :

Saurais-tu comment je peux débugger ça stp ?
Merci encore!

 

[Hasco]

Bonjour,

Plutôt que des images, vous devriez joindre un fichier avec données et requête à problème.
La fonction semblant être incriminée est une fonction native. Il n'y a pas de raison qu'elle ne fonctionne pas dans votre version de PQ.

Text.BetweenDelimiters - PowerQuery M

En savoir plus sur les alertes suivantes : Text.BetweenDelimiters

docs.microsoft.com

Vite fait dans le fichier ci-joint une version personnalisée.

Aidez-vous de la documentation et faites des tests.

Cordialement

 

[arake]

Bonjour,

Plutôt que des images, vous devriez joindre un fichier avec données et requête à problème.
La fonction semblant être incriminée est une fonction native. Il n'y a pas de raison qu'elle ne fonctionne pas dans votre version de PQ.


Vite fait dans le fichier ci-joint une version personnalisée.

Aidez-vous de la documentation et faites des tests.

Cordialement

Merci Roblochon!

Je m'aide en effet de tout ce que je peux trouver sur internet. C'est tellement vaste qu'un petit coup de pouce pour orienter la recherche est très apprécié.

Je suis en train d'étudier les étapes que tu as appliqué pour réaliser ton document, et parallèlement je découvre et me renseigne sur le PQ. Je suis sidéré d'être passé à côté d'une fonctionnalité d'excel aussi puissante...et je n'en suis qu'à mes premiers pas...

Merci encore

 

[arake]

Bonjour

Très fort la fonction qui opère sur la table imbriquée : cela optimise vraiment

Chapeau !

(J'arrive au même résultat mais avec une foultitude d'étapes)

Bonjour Chris,

Suite à la réponse de Roblochon, je suis reparti de 0 en essayant de reproduire les étapes qu'il a appliqué.
Et je bloque complet à la fonction fnLigne.
Voici le document que j'utilise.

Est-ce que je pourrais voir quelles sont les étapes que tu as appliqué stp ?

Merci pour votre aide

 

[Hasco]

Bonjour,

A moins que vous souhaitiez que ce soit @chris qui vous réponde, je peux vous guider pour l'application de la fonction fnLigne.

Dans la 5ème étape de votre requête, nommée 'Personnalisée ajoutée', vous avez oublié de modifié le nom de colonne que l'interface vous proposait. Solution :

double cliquez sur le nom de l'étape, l'interface va vous reproposer les options de nouvelle colonne, dans la zone 'Nouveau nom de la colonne', mettez : "Type ligne" (en respectant la casse)
Validez.

Vous pouvez également le faire dans la barre de formules de Power query. Si cette dernière n'apparaît pas, cliquez sur l'onglet affichage et cochez 'Barre de formule' :

Vérifiez que les étapes suivantes ne comportent plus "Custom" en nom de colonne mais bien "Type ligne".
Si "Custom" apparaît entre crochets, mettez [#"Type ligne"].

Ensuite vous pourrez faire appel à la fonction fnLigne, qui trouvera la colonne 'Type ligne'

Cordialement