Comment comparer deux colonnes dans Excel et trouver correspondances et différences

Vous avez deux listes dans Excel, par exemple les codes produits de l’inventaire et ceux qui figurent dans le catalogue en ligne, et vous devez savoir lesquels sont dans les deux, lesquels manquent d’un côté et lesquels sont en trop. Vérifier à l’œil fonctionne avec dix lignes ; avec cinq cents, il est facile d’en rater.

Voici les méthodes les plus utiles pour comparer deux colonnes, de la comparaison ligne par ligne à l’extraction automatique des différences, avec des formules à copier telles quelles.

Les données d’exemple

Nous utiliserons cette feuille : dans la colonne A, les codes de l’inventaire ; dans la B, ceux du catalogue en ligne. Les en-têtes sont en ligne 1 et les données vont de la ligne 2 à la ligne 7.

Avant de choisir une méthode, posez-vous une question : la position compte-t-elle ? Si chaque ligne représente la même chose dans les deux colonnes (par exemple l’ancien et le nouveau prix d’un produit), comparez ligne par ligne. S’il s’agit de deux listes indépendantes dans un ordre différent, vous devez chercher chaque valeur dans l’autre colonne.

Comparer ligne par ligne

Le plus simple est de saisir une égalité en C2 et de la recopier vers le bas :

=A2=B2

Elle renvoie VRAI ou FAUX. Si vous préférez un texte plus parlant :

=SI(A2=B2;"Identique";"Différent")

Avec les données d’exemple, seules les lignes 2 et 4 affichent « Identique ». Remarquez que P-102 et P-104 figurent dans les deux colonnes, mais sur des lignes différentes : cette méthode les considère donc comme différents. Elle ne sert que lorsque les lignes sont alignées.

Distinguer majuscules et minuscules

L’opérateur = ne fait pas la différence entre « abc » et « ABC ». Si elles doivent compter comme différentes, utilisez EXACT :

=SI(EXACT(A2;B2);"Identique";"Différent")

Savoir si une valeur figure dans l’autre colonne

Quand l’ordre n’a pas d’importance, l’outil clé est NB.SI. Saisissez en C2 :

=SI(NB.SI($B$2:$B$7;A2)>0;"Dans le catalogue";"Absent du catalogue")

La formule compte combien de fois A2 apparaît dans la plage B2:B7. Si c’est plus de zéro, le code est présent. Les signes $ figent la plage pour qu’elle ne se décale pas quand vous recopiez la formule. Résultat : P-101, P-102, P-103 et P-104 sont dans le catalogue ; P-105 et P-106 manquent.

Pour le sens inverse (ce qui est dans le catalogue mais pas dans l’inventaire), saisissez en D2 et recopiez vers le bas :

=SI(NB.SI($A$2:$A$7;B2)=0;"Absent de l'inventaire";"")

Vous découvrez ainsi P-107 et P-108.

Variante avec EQUIV

Si vous préférez les fonctions de recherche, cette formule donne le même résultat :

=SI(ESTNUM(EQUIV(A2;$B$2:$B$7;0));"Oui";"Non")

EQUIV renvoie la position de la valeur, ou #N/A si elle est introuvable, et ESTNUM transforme cela en VRAI ou FAUX. Si vous voulez aussi récupérer une donnée de l’autre liste, comme le prix affiché dans une colonne C du catalogue, utilisez RECHERCHEV ou, dans Excel 365 et 2021, RECHERCHEX.

Mettre en évidence correspondances et différences avec la mise en forme conditionnelle

Si vous préférez des couleurs à une colonne auxiliaire, la mise en forme conditionnelle est idéale.

Repérer les valeurs présentes dans les deux colonnes

1. Sélectionnez A2:B7.
2. Allez dans Accueil › Mise en forme conditionnelle › Règles de mise en surbrillance des cellules › Valeurs en double.
3. Choisissez la couleur et cliquez sur OK.

Excel colore toute valeur qui apparaît plus d’une fois dans la sélection. Attention : si une même colonne contient des doublons, ils seront aussi colorés, même s’ils ne figurent pas dans l’autre.

Repérer uniquement ce qui manque dans l’autre colonne

1. Sélectionnez A2:A7 en commençant par A2.
2. Allez dans Accueil › Mise en forme conditionnelle › Nouvelle règle.
3. Choisissez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
4. Saisissez la formule ci-dessous, cliquez sur Format, choisissez un remplissage et validez.

=NB.SI($B$2:$B$7;A2)=0

Seuls P-105 et P-106 seront colorés. Répétez l’opération sur B2:B7 avec =NB.SI($A$2:$A$7;B2)=0 pour repérer P-107 et P-108.

Extraire les différences dans une liste à part

Dans Excel 365 et Excel 2021, la fonction FILTRE renvoie directement la liste des valeurs qui remplissent une condition. Pour obtenir les codes de l’inventaire absents du catalogue, saisissez dans une cellule vide :

=FILTRE(A2:A7;NB.SI(B2:B7;A2:A7)=0;"Aucun")

Le résultat se propage vers le bas avec P-105 et P-106. Pour les correspondances, changez la condition :

=FILTRE(A2:A7;NB.SI(B2:B7;A2:A7)>0;"Aucun")

Si vos données s’agrandissent, transformez la plage en tableau avec Ctrl+L ou élargissez les plages. Évitez d’inclure des lignes vides : NB.SI compare aussi les cellules vides et pourrait ajouter des résultats vides. Dans les versions plus anciennes, utilisez la colonne auxiliaire avec NB.SI et appliquez-lui un filtre depuis Données › Filtrer.

Erreurs fréquentes lors de la comparaison de colonnes

• Espaces invisibles. « P-101 » suivi d’un espace n’est pas égal à « P-101 ». Nettoyez d’abord avec =SUPPRESPACE(A2) dans une colonne auxiliaire.
• Nombres stockés comme texte. 101 en nombre et « 101 » en texte ne correspondent pas. Si vous voyez un triangle vert dans le coin de la cellule, convertissez-les avec l’option Convertir en nombre.
• Caractères génériques dans les données. NB.SI interprète * et ? comme des caractères génériques. Si vos codes en contiennent, utilisez EQUIV avec une égalité ou EXACT.
• Oublier les signes $. Sans références absolues, la plage se décale lors de la recopie et les résultats ne sont plus fiables.
• Doublons dans une même liste. Ils faussent les comptages et la règle des valeurs en double. Si une colonne en contient, supprimez-les d’abord avec notre outil de suppression des doublons.

Quand il faut comparer des fichiers entiers

Les formules fonctionnent très bien avec deux colonnes d’une même feuille. Mais si vous avez deux versions d’un fichier, par exemple la liste de prix du mois précédent et l’actuelle, avec de nombreuses colonnes et lignes, construire des formules pour chaque champ devient fastidieux. Dans ce cas, vous pouvez utiliser notre outil gratuit pour comparer des fichiers Excel : vous envoyez le fichier d’origine et le fichier modifié, et vous obtenez un nouveau fichier avec les changements entre les deux, sans écrire la moindre formule.

Conclusion

Pour comparer deux colonnes, commencez par décider si la position compte. Si les lignes sont alignées, =A2=B2 ou SI suffit. S’il s’agit de listes indépendantes, NB.SI vous dit ce qui est dans les deux et ce qui manque, la mise en forme conditionnelle le rend visible et FILTRE vous donne les différences dans une liste propre. Et avant tout, vérifiez les espaces, les types de données et les doublons : ce sont eux qui causent la plupart des résultats étranges.

Questions fréquentes