comparaison de colonnes

[partfranc]

bonjour
utilisateur très moyen
j'ai sur une feuille 1 de A1 à A10 une liste de noms

j'ai sur une feuille 2 de A1 à A12 une liste de noms

je voudrais par une formule simple comparer les deux feuilles et me dire les noms qui sont dans la feuille 2 et qui ne sont pas dans la feuille 1 ou les noms qui ne sont plus dans la feuille 2 et qui étaient dans la feuille 1

j'ai recherché sur plusieurs sites mais ne trouve pas de réponse claire expliquée et qui marche
merci d'avance
excel 2003 vista

 

[Excel-lent]

Re : comparaison de colonnes

Bonjour et bienvenu sur ce forum Partfranc

Question très clair, mais si tu avais pu faire un petit fichier, je t'aurais répondu encore plus vite

Voici ci-joint une solution utilisant la MFC (Mise en Forme Conditionnel), mais si tu préfère, il est possible de faire autrement

Astuce :
Lors de l'utilisation d'une MFC, Excel ne permet pas de faire référence à une autre feuille. Or ton cas de figure le nécessite!!!!

Mais il y a une parade, il suffit de nommer tes cellules
-> sélectionner ta liste 1 sur la feuille 1 puis "insertion" / "nom" / "définir" et donner par exemple le nom : ListeFeuille1
-> sélectionner ta liste 2 sur la feuille 2 puis "insertion" / "nom" / "définir" et donner par exemple le nom : ListeFeuille2


Et maintenant tu peux faire ta MFC. Voici les fonctions que j'ai utilisé :
-> SI() : je suppose que tu l'as connais? Pas besoin d'explication?
-> NB.SI() : permet de compter le nombre de cellule correspondant au critère voulu.

MFC de la feuille 1 cellule A1 :

=SI(NB.SI([COLOR="Blue"]ListeFeuille2[/COLOR];A1)>0;VRAI;FAUX)

-> Si sur dans liste de l'autre feuille le mot de cette cellule est présent au moins une fois, alors colorier la cellule A1


MFC de la feuille 2 cellule A1 :

=SI(NB.SI([COLOR="Red"]ListeFeuille1[/COLOR];A1)>0;VRAI;FAUX)

-> Si sur dans liste de l'autre feuille le mot de cette cellule est présent au moins une fois, alors colorier la cellule A1

Si besoin de plus de détail, n'hésite pas.

Bonne après midi

Edition : bonjour Robert

 

[Robert]

Re : comparaison de colonnes

Bonjour PartFranc, Excel-lent, bonjour le forum,

Je me permets quand même de t'envoyer cette petite macro commentée même si une solution par formule reste plus efficace :

Sub Macro1()
Dim cel As Range 'déclare la variable cel (CELlule)
Dim pl1 As Range 'déclare la variable pl1 (PLage 1)
Dim pl2 As Range 'déclare la variable pl2 (PLage 2)
Dim r As Range 'déclare la variable r (Recherche)
Dim pa As String 'déclare la variable pa (Première Adresse)
Dim mes As String 'déclare la variable mes (MESsage)
 
Application.ScreenUpdating = False 'masque les changements à l'écran
Set pl1 = Sheets("Feuil1").Range("A1:A" & Sheets("Feuil1").Range("A65536").End(xlUp).Row) 'définit la plage pl1
Set pl2 = Sheets("Feuil2").Range("A1:A" & Sheets("Feuil2").Range("A65536").End(xlUp).Row) 'définit la plage pl2
For Each cel In pl1 'boucle 1 : sur toutes les cellules éditées cel de la plage pl1
    Set r = pl2.Find(cel.Value) 'définit la variable r (recherche des occurrences de cel dans la plage pl2)
    If r Is Nothing Then 'condition : si aucune occurrence de cel n'est trouvée dans la plage pl2
        mes = mes & cel.Value & " n'est pas dans l'onglet Feuil2" & Chr(13) 'définit la message
    Else 'sinon
        pa = r.Address 'définit la variable pa
        Do 'éxécute
            r.Interior.ColorIndex = 3 'colore la cellule de l'occurrence trouvée
            Set r = pl2.FindNext(r) 'redéfinit la variable r (Recherche occurrence suivante)
        Loop While Not r Is Nothing And r.Address <> pa 'boucle tant qu'il existe d'autres occurrences ailleurs qu'en pa
    End If 'fin de la condition
Next cel 'prochaine cellule cel de la plage p1
mes = mes & Chr(13) 'redéfinit le mnessage (saut de ligne)
 
For Each cel In pl2 'boucle 2 : sur toutes les cellules cel de la plage p2
    If cel.Interior.ColorIndex <> 3 Then 'condition : si la cellule n'est pas colorée
        mes = mes & cel.Value & " n'est pas dans l'onglet Feuil1" & Chr(13) 'redéfinit le message
    End If 'fin de la condition
Next cel 'prochaine cellule cel de la plage pl2
pl2.Interior.ColorIndex = 0 'enlève la couleur éventuelle de la plage pl2
Application.ScreenUpdating = True 'afiche les changements à l'écran
MsgBox mes 'affiche le message
  
End Sub

Attention ! il faut que les onglet s'appellent bien Feuil1 et Feuil2 sinon la macro ne fonctionnera pas...

À la fin un messagera t'indiquera les noms qui sont dans Feuil1 et pas dans Feuil2 et aussi les noms qui sont dans Feuil2 et pas dans Feuil1.

Hé le prix du gaz va baisser...!

 

[partfranc]

Re : comparaison de colonnes

Merci à Excel lent de sa réponse mais je n'arrive pas à la faire marcher
j'ai donné un nom comme tu me l'as dit à mes deux listes
j'ai sur la feuille 1 de A1à A3 jean jacques jules et sur la feuille 2 de A1à A4 jean jacques jules jim
où dois je copier tes deux codes et dois je respecter les couleurs utilisés
je suis probablement très inculte mais ?
merci d'avance

 

[partfranc]

Re : comparaison de colonnes

je me reprens j'ai cliqué sur la cellule A1 de la feuille 1 puis je suis allé dans format mfc et là j'aifait la formule est et j'ai copié la première formule que tu m'as donné puis choisi une couleur puis fait ok mais rien ne se passe
je progresse un peu mais...

 

[Excel-lent]

Re : comparaison de colonnes

Bonsoir Partfranc,

L'idéal, pour savoir où tu coince, j'aurais besoin que tu mette ton fichier ici.

Source d'erreur possible :
-> tu t'es trompé lorsque tu as nommée tes zones de cellule
-> tu t'es trompé dans la formule de MFC soit dans le nom des cellules, soit dans la formule en elle même

Pour le format : couleur de la police, couleur de la cellule, bordures, polices, ... tu peux faire tout ce que tu veux (tu n'es pas obligé faire comme dans l'exemple).

A te lire

 

[Excel-lent]

Re : comparaison de colonnes

(supprimé : doublons)

 

[partfranc]

Re : comparaison de colonnes

je t'ai joint le fichier que tu m'as demandé

 

[chris]

Re : comparaison de colonnes

Bonjour

Le PB est dans la mise en forme conditionnelle : il y a des guillemets et cela n'est pas considéré comme une formule.

Enlève-les.

Tu peux simplifier la formule ainsi
=NB.SI(ListeFeuille1;A1)>0

 

[partfranc]

Re : comparaison de colonnes

merci de ta réponse en enlevant les " cela marche mais ce qui est curieux c'est que je n'avais pas tapé les guillemets et qu'ils se sont mis une fois le texte tapé
merci en tout cas
peux tu m'expliciter la signification des deux formules
=NB.SI(ListeFeuille1;A1)>0
=SI(NB.SI(ListeFeuille1;A1)>0;VRAI;FAUX)

que veux dire le >0 et NB.SI et vrai; faux??

dans l'aide d'excel le NB.si semble compter le nombre de cellules

à plus

 

[chris]

Re : comparaison de colonnes

Bonjour

en enlevant les " cela marche mais ce qui est curieux c'est que je n'avais pas tapé les guillemets

Tu avais sans doute oublié quelque chose lors de la frappe et Excel, qui a horreur du vide ;-) a complété à son idée.

peux tu m'expliciter la signification des deux formules
=NB.SI(ListeFeuille1;A1)>0
=SI(NB.SI(ListeFeuille1;A1)>0;VRAI;FAUX)

que veux dire le >0 et NB.SI et vrai; faux??

dans l'aide d'excel le NB.si semble compter le nombre de cellules

NB.SI compte le nombre de cellule répondant à une condition ou critère.
Ici on compte dans la plage ListeFeuille1 le nombre de cellules ayant la même valeur que A1 (de la feuille2) : si on en trouve plus de 0 c'est donc que la valeur de A1 est présente dans la feuille1.
(et ainsi de suite puisque pour la ligne 2, c'est A2 qu'Excel utilisera).

Dans la forme longue de la formule : on dit que si ce nombre est supérieur à 0 alors c'est vrai sinon c'est faux.
Mais la MFC ignore automatiquement toute condition non vérifiée d'où ma simplification qui donne en Français : au cas où le nombre est >0 alors formater la cellule.

 

[Excel-lent]

Re : comparaison de colonnes

Bonsoir le fil,

vrai; faux??

Lorsque la formule est "vrai" : il applique la police, la couleur, ... que tu as choisi

Lorsque la formule est "faux" : Excel ne fait rien

Mais comme Chris te l'a très justement montré, tu peux également te passer de cette partie.

Je l'avais mise pour que ce soit plus clair, mais à priori, je me suis trompé!!

Bonne soirée et bon WE à tous

 

[sam4444]

Re : comparaison de colonnes

Bonjour, je n'arrive pas à comparer 2 colonnes sur la même feuille. Je souhaite afficher seulement les différences entre ces 2 colonnes en C (a vs B) et en D (b vs A). Jai mis ceci en C : =SI(A2="","",SI(ESTNA(EQUIV(A2,B$2:B$1501,0)),A2,"")) et en D =SI(B4="","",SI(ESTNA(EQUIV(B4,A$2:A$1501,0)),B4,"")). mais ça ne fonctionne pas.

Merci de votre aide.

SAM