Consolider deux listes de données sans doublons

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fusionner deux listes sans doublons

Dans ce nouveau chapitre, nous proposons de créer une fonction VBA Excel capable de réunir les valeurs de deux listes de données, en excluant les répétitions avec une rangée résultante automatiquement triée.

Pour développer cette fonction, nous suggérons d'appuyer l'étude sur un classeur Excel abritant deux listes de données offrant quelques répétitions à ignorer.

• Télécharger le classeur consolider-2-listes.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier résultant,
• Dans le menu contextuel, choisir la commande Propriétés,
• En bas de la boîte de dialogue, cocher la case Débloquer puis valider par Ok,
• Dès lors, double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons les deux listes de données. Les valeurs communes, donc les doublons, y sont repérés en couleur par deux règles de mise en forme conditionnelle exploitant la fonction Nb.Si dans les deux cas. La consolidation des deux rangées, sans doublons, doit intervenir dans une troisième liste en colonne F, grâce à une fonction VBA Excel à développer.

Créer la fonction VBA
Cette fonction doit être signée avec deux paramètres en attente. Il s'agit des deux plages de données à consolider, que l'utilisateur devra fournir au moment de la construction de la formule.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, double cliquer sur l'élément Module1,
• Dans sa feuille de code au centre, créer la fonction unirListes, comme suit :

Function unirListes(liste1 As Range,liste2 As Range) As Variant

End Function

Les deux arguments en attente sont bien évidemment déclarés comme des objets de type Range (Plages de cellules). La fonction est elle-même typée comme un variant, soit un objet qui n'a pas encore pris son genre. En effet, c'est une fonction matricielle que nous allons construire. Elle ne doit pas répondre par un seul résultat mais par une multitude de valeurs.

Les variables
Pour comparer deux listes et les consolider sans doublons, nous avons besoin de variables, notamment pour piloter les deux plages de cellules passées en arguments de la fonction que nous sommes en train de construire.

• Dans les bornes de la fonction, ajouter les déclarations suivantes :

...
Dim plage1 As Range: Dim plage2 As Range: Dim plageR As Variant
Dim ligne1 As Byte: Dim ligne2 As Byte: Dim nbLignes As Integer
Dim cellule As Range: Dim cellule2 As Range: Dim test As Boolean
...

Hormis les deux premiers objets pour représenter les plages à analyser, nous en créons un autre que nous nommons plageR. Il se transformera en tableau de valeurs, celles des deux listes consolidées sans doublons et à retourner par la fonction. Ensuite, nous déclarons des variables pour représenter les lignes à implémenter (ligne1 et ligne2) dans ce tableau résultant. La variable nbLignes doit servir à connaître le nombre total de lignes à traiter et potentiellement à retourner lorsque les deux listes ne proposent aucun doublon. Nous exploiterons les objets cellule et cellule2 pour parcourir les cellules des deux plages à confronter. Enfin, le booléen test doit servir d'indicateur pour savoir si une donnée analysée a déjà été observée dans l'autre liste, afin de ne pas la répéter.

Piloter les plages de cellules
A présent, nous devons notamment initialiser les deux objets de type Range pour piloter les deux listes de données passées en paramètres de la fonction.

• Après les variables, ajouter les instructions VBA suivantes :

...
Set plage1 = liste1: Set plage2 = liste2
nbLignes = plage1.Rows.Count + plage2.Rows.Count
ReDim plageR(1 To nbLignes)
ligne1 = 1
...

Grâce à ces deux initialisations (Set), nous pouvons exploiter la propriété Count de la collection Rows sur les deux listes. C'est ainsi que nous stockons dans la variable nbLignes, le nombre de cellules à analyser. Grâce à la fonction Redim, nous exploitons ce nombre pour définir la hauteur du tableau de données à retourner. Puis, nous initialisons la variable ligne1 sur le premier indice (1) du tableau.

Toutes les cellules de la première liste
Maintenant, nous pouvons parcourir toutes les cellules de la première liste pour les ajouter dans le tableau plageR. C'est quand nous parcourrons la seconde que nous vérifierons si la donnée analysée est présente dans la première pour ne pas l'ajouter en double.

• Toujours à la suite du code VBA, créer la boucle For Each suivante :

...
For Each cellule In plage1
plageR(ligne1) = cellule.Value
ligne1 = ligne1 + 1
Next cellule
...

Nous exploitons l'objet cellule pour parcourir toutes les cellules dans (In) la première liste (plage1). Nous les ajoutons tour à tour dans le tableau plageR sans oublier d'incrémenter la variable ligne1, pour déplacer le pointeur sur la prochaine rangée du tableau.

Parcourir les cellules de la seconde liste
Maintenant, nous devons analyser toutes les cellules de la seconde plage. C'est dans un second temps que nous envisagerons de les confronter avec celles qui ont déjà été ajoutées et qui sont issues de la première liste. Donc, nous devons dégainer une nouvelle boucle For Each.

• Toujours à la suite du code VBA, créer la nouvelle boucle For Each suivante :

...
For Each cellule In plage2
test = False

Next cellule
...

Il s'agit d'une amorce. A chaque passage, donc pour chaque nouvelle cellule de la seconde liste, nous réinitialisons le booléen à False. En effet et tant que nous n'avons pas encore entamé la comparaison de sa valeur avec toutes celles de la première liste, nous considérons encore qu'elle n'est pas dupliquée.

Comparer les deux listes
Dans cette seconde boucle For Each, nous devons imbriquer une autre boucle. Son rôle est de comparer toutes les cellules de la première liste avec celle en cours d'analyse dans la seconde. Si la correspondance est trouvée, le booléen doit être basculé à True. C'est cet indicateur qui nous permettra de ne pas ajouter la donnée redondante à la suite dans la plage consolidée.

• A la suite du code de la boucle, créer la nouvelle boucle suivante :

...
For Each cellule In plage2
test = False
For Each cellule2 In plage1
If cellule.Value = cellule2.Value Then test = True
Next cellule2
Next cellule
...

Pour chaque cellule de la première liste (For Each cellule2 In plage1), si une correspondance est observée avec la cellule en cours d'analyse dans la seconde liste, nous basculons le booléen à True.

Consolider les données
C'est ainsi, si la cellule en cours d'analyse dans la seconde liste n'a pas été décelée dans la première, donc que le booléen est resté à false, que nous pouvons ajouter son information à la suite dans la liste consolidée (plageR). Bien sûr, cette consolidation doit intervenir après la seconde boucle mais toujours à l'intérieur de la première.

• Après la seconde boucle, ajouter l'instruction conditionnelle suivante :

...
Next cellule2
If test = False Then
plageR(ligne1) = cellule.Value
ligne1 = ligne1 + 1
End If
Next cellule
...

Tester la consolidation
La construction de la fonction n'est pas encore terminée et nous allons vite comprendre pourquoi. Pour qu'elle puisse répondre, nous devons tout d'abord l'affecter sur la plage consolidée.

• A la fin de la fonction, ajouter l'affectation suivante :

...
Next cellule

unirListes = plageR

End Function
...

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille Excel (ALT + Tab),
• Sélectionner la case du point de départ pour la consolidation, soit la cellule F4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la nouvelle fonction suivi d'une parenthèse, soit : unirListes(,
• Désigner la première liste à consolider en sélectionnant la plage B4:B19,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction,
• Désigner la seconde liste à consolider en sélectionnant la plage D4:D23,
• Fermer la parenthèse de la fonction puis valider la formule par la touche Entrée,

Les données semblent être consolidées mais elles sont réparties sur la droite, sur une même ligne plutôt que dans une seule colonne les unes en dessous des autres. De plus, si vous atteignez la fin de l'extraction sur la droite, vous notez la présence de quelques chiffres 0 en bout de course. Nous devons corriger ces problèmes.

Remplir les trous
Au début du code, nous avons dimensionné la liste plageR sur sa hauteur maximale, en considérant les tailles des deux listes à cumuler, lorsqu'elles ne présentent aucun doublon. Mais lorsque des répétitions existent, toutes ses rangées ne seront pas renseignées puisque des informations seront exclues. C'est la raison des chiffres 0 en bout de course de la fonction lorsque nous l'exploitons sur la feuille.

• Revenir dans l'éditeur de code VBA Excel,
• Avant l'affectation de la fonction, ajouter la nouvelle boucle suivante :

...
Next cellule

For ligne2 = ligne1 To nbLignes
plageR(ligne2) = ""
Next ligne2

unirListes = plageR

End Function
...

Nous partons du dernier indice de ligne renseigné (ligne1) pour rejoindre la borne finale (nbLignes) de la liste plageR. Dans ses dernières rangées, nous forçons l'écriture d'une chaîne vide ("") pour supprimer les zéros. Si vous testez de nouveau cette fonction sur la feuille, vous constatez que les zéros en bout de course, disparaissent.

Retourner et trier
Il nous reste deux petits réglages à effectuer. Tout d'abord, nous souhaitons inscrire les résultats de la fonction matricielle dans une seule colonne. Nous devons donc transposer la liste résultante. Puis, pour constater plus facilement la disparition des doublons, nous souhaitons regrouper les données en les triant. Dans les deux cas, nous proposons d'exploiter des fonctions de feuille de calcul par le code VBA.

• A la fin de la fonction, remplacer son affectation par les deux lignes VBA suivantes :

...
Next ligne2

plageR = WorksheetFunction.Transpose(plageR)
unirListes = WorksheetFunction.Sort(plageR, 1, -1)

End Function
...

Si vous actualisez de nouveau la fonction sur la feuille, cette fois, vous avez le plaisir de constater que tous les résultats sont livrés à la verticale, dans une seule colonne.



Les informations de retour y sont organisées par ordre alphabétique décroissant. Et c'est ainsi que nous pouvons constater avec grande aisance que les répétitions ont été exclues. Dans le même temps, les zéros en bout de course ont bel et bien disparu.