Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

[emi06]

Bonjour à tous,

J'ai quelques bases en VBA mais j'en reste quand même une novice, c'est pourquoi je fais appel à votre savoir

Voilà mon problème: dans un classeur Excel, j'ai 3 feuilles:
- la 1ère contient le sommaire d'un référentiel avec sa hiérarchisation (colonne A) ainsi que les thèmes abordés dans chaque paragraphe (colonne B); il s'agit de la version 2009 du référentiel,
- la 2nde, c'est la même chose mais pour la version 2014 du même référentiel (la hiérarchisation n'est donc pas la même -ajout ou suppression de paragraphes-, tout comme certains thèmes qui peuvent apparaître ou disparaître),
- pour la 3ème (et c'est là que j'ai besoin de vous !), je voudrai que selon le ou les thèmes choisis, il ressorte les paragraphes correspondants de la version 2009 et de la version 2014.

Je vous explique un peu plus précisément le cas de ma feuille 3, il s'agirait de:

- dans un 1er temps, collecter l'ensemble des thèmes énumérés dans les 2 versions puis en faire une liste déroulante en A2, B2 et C2, sans doublon (je pourrai donc préciser mes recherches au fur et à mesure en ajoutant un ou plusieurs critères: les thèmes - je choisis d'avoir 3 critères maximum)

- en fonction du ou des thèmes choisis, il faudrait qu'en D2 (pour la version 2009) et E2(pour la version 2014) apparaisse l'ensemble des paragraphes pouvant répondre aux critères, c'est-à-dire uniquement la numérotation se trouvant en colonne A des feuilles 1 et 2 (les uns en dessous des autres si possible)

Il s'agirait en fait pour moi d'un outil simple de comparaison de 2 versions d'un même référentiel.

Je vous mets en PJ une ébauche de ce classeur.

Merci d'avance pour votre aide Si vous ne comprenez pas tout, n'hésitez pas à me poser des questions.

A Bientôt !

 

[Modeste]

Re : Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

Bonsoir emi06,

Si vous ne comprenez pas tout, n'hésitez pas à me poser des questions

Quelle bonne idée!
- Comment savoir où trouver les thèmes et comment les reconnaître? Dans tes listes, il y a des titres, des sous-titres et des thèmes; ces derniers sont toujours au "niveau 3" et suivants?
- Dans une cellule, ils sont toujours séparés par des virgules? N'y a-t-il pas, dans ces mêmes cellules, d'autres virgules (qui ne seraient pas des séparateurs?)
- Quand tu évoques "l'ensemble des thèmes énumérés dans les 2 versions", tu veux parler des thèmes qui sont communs aux deux ou de tous ceux qui figurent indistinctement dans l'une ou l'autre?
- Si on répertorie en colonnes D et E les différents paragraphes (tu parles des n°, je suppose?), il faudra tout de même indiquer les thèmes aussi, non? Et faire en sorte que thème, paragraphe en 2009 et paragraphe en 2014 soient les uns en face des autres?
- Le volume total (en tout cas le contenu des 2 feuilles de départ) ça représente combien de lignes?

C'est aussi "monstrueux" que ça en a l'air, ou c'est juste une impression?

 

[emi06]

Re : Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

Bonjour Modeste,

Mais non ce n'est pas si monstrueux que ça ! ... Fin j'espère ^^

Pour répondre à tes questions:

1. Effectivement, les thèmes sont toujours en niveau 3 et suivants (càd, 1.1.1 et 1.1.1.1., grand maximum) je ne m'intéresse donc uniquement qu'aux cellules non colorées (celles qui le sont correspondent aux titres et sous-titres). Ces thèmes sont donc comme dans le fichier exemple, en colonne B.
Voici un exemple concret pour la version 2009 (feuille 1): le paragraphe 2.2.3 (en colonne A) traite des thèmes suivants: sous-traitants, équipements, disponibilité (en colonne B).

2. Oui, ils sont toujours séparés par une virgule dans chaque cellule, exactement comme dans l'exemple ci-dessus. Et qu'entends tu par "ne seraient pas des séparateurs" ?

3. Je veux récupérer l'ensemble des thèmes qui apparaissent à la fois dans la V2009 et dans la V2014, mais en unique exemplaire afin d'en faire une liste défilante, donc sans doublon. Certains thèmes seront propres à la V2009, d'autres propres à la V2014 et d'autres en commun aux 2 versions. Pour ce dernier cas, je veux juste que le thème apparaisse une seule fois dans la liste défilante qui finalement me retourne l'ensemble des thèmes.

4. Bah les thèmes sont ceux que j'aurai choisi comme critères en colonnes A, B et C (de la feuille 3), donc non, en colonne D et E (feuille 3), seul(s) le(s) numéro(s) de paragraphe (correspondant à la numérotation hiérarchique des colonnes A des feuilles 1 et 2) suffisent.
Exemple (je me base sur celui utilisé en 1.): mes choix de critères sont les suivants: thème 1 "sous-traitants" (colonne 4, feuille 3), thème 2 "équipements" (colonne B, feuille 3), thème 3 "disponibilité" (colonne C, feuille 3) (bien évidement, si je ne veux qu'1 ou 2 critères, c'est aussi possible). Ainsi, en colonne D (pour la version 2009) est retourné "2.2.3." et peut-être d'autres paragraphes qui pourraient correspondre à ces critères. De la même façon, en colonne E (pour la version 2014), les numéros de paragraphes répondant à ces 3 critères sont aussi affichés. C'est un peu le même système que si tu cherches un appart' à louer sur "SeLoger.com", tu mets tes critères (budget, surface, localisation, etc) et l'ensemble des offres répondant à ces critères s'affichent ^^ (ça te parleras p'têtre plus ).

5. Pour la feuille 1, il y a environ 200 lignes et pour la feuille 2, environ 300 lignes (il y a plus d'exigences dans le nouveau référentiel).

J'espère avoir été assez claire, si malheureusement des incompréhensions persistent, n'hésite pas à nouveau

Encore merci pour ton aide

 

[Modeste]

Re : Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

Bonjour emi06, bienaimé forum,

Je ne sais pas ce que j'aurai le temps de faire aujourd'hui ... voilà donc déjà un bout (si j'ai bien compris!)

Je me suis basé sur les cellules sans couleur de remplissage, pour "garnir" un objet "dictionary" (idéal pour la gestion des doublons). Le code est (un peu) commenté.
Une plage nommée "Thèmes" (en colonne M de la feuille "Comparaison") reçoit les "keys" du dico et sert de source aux validations de A2, B2 et C2. Une mise en forme conditionnelle sur ces 3 cellules affiche la cellule en rouge, si on sélectionne le même thème dans 2 cellules.

Jette déjà un premier coup d'œil, vérifie si j'ai compris correctement, teste avec d'autres données, puis intéresse-toi au code

Je repasserai plus tard voir si tu as des questions ou remarques (ou si quelqu'un a pris le relais ou fait une autre proposition)

Ma question sur les virgules qui ne seraient pas "séparateurs" était la suivante: les thèmes sont toujours séparés par des virgules (tu l'as confirmé). Mais le "nom" d'un thème peut-il contenir une virgule? J'utilise la virgule pour "splitter" les différents thèmes renseignés dans tes cellules. S'il y a une autre virgule (par exemple un seul thème aurait pour nom: "équipements, machines et fournitures" ou "canons 7,65 mm"), tu vas te retrouver avec deux thèmes distinct! (dans le dernier exemple cité: un thème "canons 7" et un autre "65 mm"). Tu me suis?

Quant à ma dernière question, illustrons-la:
Tu t'intéresses aux thèmes 1 et 12. Tu obtiens donc, en colonne E (pour v2009, donc):
1.3.1
1.3.2
1.3.2.1
1.3.2.2
1.3.2.3
2.2.1
2.2.2
Comment détermines-tu, après coup, que le 1.3.2.2 concerne le thème 1, le thème 12 ou les deux en même temps?

 

[emi06]

Re : Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

Bonjour Modeste,

Ahhh, je comprends mieux la nuance pour la virgule Mais en effet, un thème reste un mot entier sans quelconque séparateur.

Pour ta dernière question, attention !
Je continue avec ton exemple du thème 1 et du thème 12. Quand on choisit de prendre 2 critères (un en colonne A et un en colonne B), c'est qu'on souhaite ressortir les paragraphes des V2009 et V2014 traitant A LA FOIS de ces 2 thèmes.
Ainsi, si on souhaite mettre qu'un seul thème (en colonne A, feuille 3) alors dans ce cas il faudrait qu'en colonne B et en colonne C, il y ait une autre possibilité de choix neutre tel que "pas de thème". Il ressortirait donc en colonne E et F l'ensemble des paragraphes des V2009 et V2014 qui traiteraient de ce thème 1.

Je résume l'enchainement des choses:
Si on part du principe qu'on choisit qu'un seul thème (colonne A, feuille 3), par exemple thème 1 "sous-traitants", alors dans un 1er temps l'ensemble des paragraphes traitant du thème 1 de la V2009 et de la V2014 s'affichent en colonne E et F. Dans ce cas, on aurait sélectionner le choix "pas de thème" dans les listes déroulantes en colonne B et C de la feuille 3.
Ensuite si on veut ajouter un 2nd critère en colonne B (feuille 3), par exemple le thème 2 "équipement" alors l'ensemble des paragraphes traitant à la fois des 2 thèmes s'affichent (càd thème 1 "sous-traitants" et thème 2 "équipement").
Le but n'est pas de savoir quel paragraphe traite uniquement du thème 1 OU du thème 2 (ça on l'a directement si on choisit de mettre qu'un seul critère en colonne A), mais bien de savoir quels paragraphes regroupent le thème 1 ET le thème 2 dans les V2009 et V2014.

Je reviens donc sur ton 1er jet, plutôt pas mal
Juste 2 petites choses:
1. Comme je viens de te soumettre l'idée, si je veux mettre qu'un seul ou 2 critères maxi (donc en A, ou en A et B), je ne peux pas le faire car les listes déroulantes en B et C ne contiennent pas de choix tel que "pas de thème" ou "thème neutre", qqch dans le genre. Il faudrait donc avoir la possibilité de ne pas choisir de thème spécifique en colonne B et C.
2. Lorsque j'active la macro et que je fais le choix de mes critères (je vérifie bien que l'association des 3 critères existe en feuille 1 et/ou feuille 2), malheureusement rien ne s'affiche en colonne E et/ou F. Je ne pense pas que cela soit normal, d'où est ce que le pb peut venir ?

Encore merci pour ton aide précieuse

A bientôt !

 

[Modeste]

Re : Aide pour comparaison de 2 feuilles excel puis association d'info en feuille 3

Bonjour emi06, le fil et le forum,

Un second "état d'avancement" ... je ne me suis occupé que de rechercher les thèmes de 2009 (ça m'a déjà suffi : il est possible que je m'y sois pris "comme un manche" surtout, que personne ne commente cette hypothèse!! mais ça m'a soudain semblé plus compliqué que je ne l'avais cru )

J'ai utilisé un tableau d'objets "Dictionary" pour la recherche des thèmes communs.
La bonne nouvelle, c'est qu'ajouter la recherche pour la feuille 2014 ne devrait plus être qu'une formalité (ou presque ... je reste prudent, maintenant )
Ceci dit, l'exercice aurait pu être plus aisé avec tes "vrais" thèmes: dans le présent exemple, "thème1" est considéré comme partie de la chaîne "thème5, thème10, thème8" ... au moins, si ça fonctionne ici, ça devrait aussi fonctionner avec ton fichier "réel".

La liste des thèmes en colonne M se met à jour lors de l'activation de la feuille "Comparaison". Il faudrait vérifier s'il ne serait pas utile d'effacer les cellules A2:C2 au même moment? J'ai modifié les thèmes présents dans les feuilles 2009 et 2014, pour faire quelques tests.

La liste des thèmes communs est modifiée chaque fois que le contenu d'une des 3 mêmes cellules est modifié. Attention de les compléter depuis la cellule le plus à gauche (autrement dit, ne pas indiquer un thème en B2, si A2 est vide!)

Teste d'abord avec tes données exemples de la pièce jointe, puis, si ça fonctionne, essaye avec d'autres données. Tente ensuite d'en ajouter un peu plus ... et reviens en courant si quelque chose "coince" (mais cours plus vite encore pour nous dire si ça fonctionne! )