Chercher des intrus dans une colonne

[josanche]

Bonjour le forum,

je travaille sur excel 2010 en anglais et j'aimerais savoir si il existe une formule qui renvoit les cellules qui n'existe pas dans une colonne de référence. En pièce jointe, vous pourrez retrouver un exemple. J'ai deux colonnes. Ma colonne de référence X et la colonne à tester, la colonne Y. Je voudrais voir si il existe dans ma colonne Y des cellules qui n'existe pas dans ma colonne de référence X.

Veuillez retrouver mon fichier excel en pièce jointe. En voyant le fichier, vous pourrez facilement y répondre.

Cordialement

Merci d'avance pour votre aide.

 

[Dugenou]

Re : Chercher des intrus dans une colonne

Bonjour,
en pj deux colonnes : une avec une formule simple qui indique sur chaque ligne la présence d'intrus, une autre (matricielle) qui affiche la liste comme dans ton exemple.
Cordialement

 

[R@chid]

Re : Chercher des intrus dans une colonne

Bonjour,
en D3,

=SIERREUR(INDEX(B$3:B$9;PETITE.VALEUR(SI(NB.SI(A$3:A$9;B$3:B$9)=0;LIGNE(INDIRECT("1:"&LIGNES(B$3:B$9))));LIGNES($3:3)));"")

@ valider par Ctrl+Maj+Entree
@ tirer vers le bas
@ + +

Edit : Salut Dugenou 🙂

 

[josanche]

Re : Chercher des intrus dans une colonne

recoucou dugenou, et coucou rachid

Bon raisonnement logique, j'avais pas pensé faire comme ça !!! merci 🙂)))

 

[josanche]

Re : Chercher des intrus dans une colonne

Si au lieu d'avoir une colonne de référence, on a deux colonnes de référence, la formule suivante est-elle juste ???

=IF(AND(COUNTIF($D$1:$D$22392;A1);COUNTIF($E$1:$E$22392;B1))=0;"intrus";"")

 

[job75]

Re : Chercher des intrus dans une colonne

Bonjour josanche,

Voyez le fichier joint avec cette formule matricielle en D3 :

=SI(LIGNES(D$3:D3)>SOMME(N(NB.SI(A$3:A$9;B$3:B$9)=0));"";INDEX(B$3:B$9;PETITE.VALEUR(SI(NB.SI(A$3:A$9;B$3:B$9)=0;LIGNE(B$3:B$9)-LIGNE(INDEX(B$3:B$9;1))+1);LIGNES(D$3:D3))))

ou sur une version anglaise :

=IF(ROWS(D$3:D3)>SUM(N(COUNTIF(A$3:A$9,B$3:B$9)=0)),"",INDEX(B$3:B$9,SMALL(IF(COUNTIF(A$3:A$9,B$3:B$9)=0,ROW(B$3:B$9)-ROW(INDEX(B$3:B$9,1))+1),ROWS(D$3:D3))))

A valider par Ctrl+Shift+Enter et tirer vers le bas.

Nota : on peut nommer les plages et même utiliser des noms dynamiques.

Edit : salut à tous, très en retard, je n'avais pas rafraîchi.

A+

 

[Dugenou]

Re : Chercher des intrus dans une colonne

Re Josanche,
cela dépends de ce que vous manipulez comme données et ce qu'on nomme un intrus : avec votre calcul on ne détectera pas un couple absent de la liste mais dont les composants sont présents séparément.
Voir pj

R@chid : si tu continues de copier je le dis à la maitresse ! 🙂

 

[josanche]

Re : Chercher des intrus dans une colonne

Dugenou, je vous remercie pour me montrer la différence entre V1 et V2. V2 est bcp plus restrictif et c ce que je recherchais.
Merci job75. mais je pense que la formule de dugenou est plus simple. j'ai testé et ça a résolu mon probleme

 

[klin89]

Re : Chercher des intrus dans une colonne

Bonsoir à tous, 🙂

Résultat à partir de E3 :

Sub Intrus()
Dim temp()
  Set MonDico1 = CreateObject("Scripting.Dictionary")
  For Each c In Range("a3:a" & [a65536].End(xlUp).Row)
    If c <> "" Then MonDico1.Add c.Value, c.Value
  Next c
  Set mondico2 = CreateObject("Scripting.Dictionary")
  For Each c In Range("b3:b" & [b65536].End(xlUp).Row)
    If Not MonDico1.Exists(c.Value) Then
      If c <> "" Then mondico2.Add c.Value, c.Value
    End If
  Next c
  If mondico2.Count = 0 Then Exit Sub
  ReDim temp(1 To mondico2.Count)
  i = 1
  For Each c In mondico2.items
    temp(i) = c
    i = i + 1
  Next
  [e3].Resize(mondico2.Count, 1) = Application.Transpose(temp)
End Sub

Klin89