comparaison de deux colonnes dans excel

gorbachuv · 13 Mars 2014

Bonjour ;
j'ai un fichier excel 2010 contenant deux colonnes.le premier (A) contient des noms(100 noms), et le deuxième contenant des noms et des prenoms (environs 190 cellules).
je veux comparer les deux colonnes et mettre dans une troisième colonnes (C) les valeurs de la colonnes B qui ne figurent pas dans la colonnes A.
plus clair : je veux comparer deux colonnes A et B qui contiennent "des personnes" et maitre dans C les personnes que ne figure pas dans les deux colonnes A et B.
Merci de m'aider c'est urgent Pliiiizzzz.

job75 · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Bonjour gorbachuv, bienvenue sur XLD,

Nous on a tout notre temps, mais si vous voulez rapidement une réponse déposez votre fichier.

Edit : remplacez les noms par des noms bidons mais représentatifs du problème, inutile de tous les mettre.

A+

Nairolf · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Salut,

Sans fichier exemple c'est plus compliqué de te répondre correctement, mais tu peux essayer avec ceci:

Dans la colonne C tu peux faire le calcul suivant:
C=si(A=B;"";B)

Edit: Salut Job75

Dugenou · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Bonjour,
un essai en pj : mettre en colonne C les valeurs de B dont on ne trouve pas le nom en A

pour afficher les valeurs de A qui n 'apparaissent pas en B ce sera une autre formule et pour les deux ensemble ça va être compliqué

mais faisable : voir V2

job75 · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Bonjour Nairolf, Dugenou,

Pas si pressé le russe.

En attendant voyez le fichier joint avec cette macro :

Code:

Sub Résultat()
Dim i&, t, j&, flag As Boolean
Application.ScreenUpdating = False
[A:B].Copy
[C1].Insert 'insertion de 2 colonnes auxiliaires
'---suppression des doublons en 1ère colonne (C)---
For i = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1
  t = Cells(i, 3)
  For j = i - 1 To 2 Step -1
    If Cells(j, 3) = t Then Cells(i, 3).Delete xlUp: Exit For
  Next
Next
'---Comparaison des 2 colonnes C et D et suppressions---
For i = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
  t = Cells(i, 3)
  flag = False
  For j = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
    If InStr(Cells(j, 4), t) Then Cells(j, 4).Delete xlUp: flag = True
  Next
  If flag Then Cells(i, 3).Delete xlUp
Next
[C2:C1000].Copy [E2]
[D2:D1000].Copy [E1001]
[C:D].Delete
[C:C].Sort [C1], Header:=xlYes 'tri
End Sub

A+

job75 · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Re,

Avec la macro précédente il peut y avoir des doublons dans le résultat, il est bon de les supprimer :

Code:

Option Explicit
Option Compare Text 'pour ignorer la casse

Sub Résultat()
Dim i&, t, j&, flag As Boolean
Application.ScreenUpdating = False
[A:B].Copy
[C1].Insert 'insertion de 2 colonnes auxiliaires
'---suppression des doublons en 1ère colonne (C)---
For i = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1
  t = Cells(i, 3)
  For j = i - 1 To 2 Step -1
    If Cells(j, 3) = t Then Cells(i, 3).Delete xlUp: Exit For
  Next
Next
'---Comparaison des 2 colonnes C et D et suppressions---
For i = Range("C" & Rows.Count).End(xlUp).Row To 2 Step -1
  t = Cells(i, 3)
  flag = False
  For j = Range("D" & Rows.Count).End(xlUp).Row To 2 Step -1
    If InStr(Cells(j, 4), t) Then Cells(j, 4).Delete xlUp: flag = True
  Next
  If flag Then Cells(i, 3).Delete xlUp
Next
[C2:C1000].Copy [E2]
[D2:D1000].Copy [E1001]
[C:D].Delete
[C:C].Sort [C1], Header:=xlYes 'tri
'---suppression des doublons du résultat---
For i = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1
  If Cells(i - 1, 3) = Cells(i, 3) Then Cells(i, 3).Delete xlUp
Next
End Sub

Fichier (2).

A+

job75 · 13 Mars 2014

Re : comparaison de deux colonnes dans excel

Re,

Cette macro, avec le tri initial des colonnes auxiliaires, est meilleure :

Code:

Option Explicit
Option Compare Text 'pour ignorer la casse

Sub Résultat()
Dim i&, t, flag As Boolean, j&
Application.ScreenUpdating = False
[A:B].Copy
[C1].Insert 'insertion de 2 colonnes auxiliaires C et D
[C:C].Sort [C1], Header:=xlYes 'tri
[D:D].Sort [D1], Header:=xlYes 'tri
'---suppression des doublons en colonne C---
For i = Application.CountA([C:C]) To 3 Step -1
  If Cells(i - 1, 3) = Cells(i, 3) Then Cells(i, 3).Delete xlUp
Next
'---suppression des doublons en colonne D---
For i = Application.CountA([D:D]) To 3 Step -1
  If Cells(i - 1, 4) = Cells(i, 4) Then Cells(i, 4).Delete xlUp
Next
'---Comparaison des 2 colonnes C et D et suppressions---
For i = Application.CountA([C:C]) To 2 Step -1
  t = Cells(i, 3)
  flag = False
  For j = Application.CountA([D:D]) To 2 Step -1
    If InStr(Cells(j, 4), t) Then Cells(j, 4).Delete xlUp: flag = True
  Next
  If flag Then Cells(i, 3).Delete xlUp
Next
'---résultat en colonne E---
i = Application.CountA([C:C]): j = Application.CountA([D:D])
If i > 1 Then [C2].Resize(i - 1).Copy [E2]
If j > 1 Then [D2].Resize(j - 1).Copy Cells(i + 1, 5)
i = IIf(i + j < 2, 2, i + j)
Range("E" & i & ":E" & Rows.Count).Delete xlUp
[C:D].Delete 'suppression des colonnes auxiliaires
i = ActiveSheet.UsedRange.Rows.Count 'RAZ du UsedRange
[C:C].Sort [C1], Header:=xlYes 'tri facultatif
End Sub

Cela dit j'utilise cette méthode (suppression de cellules) parce qu'il y aura peu de lignes.

S'il y en avait des milliers il faudrait une autre méthode (tableaux VBA).

Fichier (3).

Edit : pour éviter tout problème, mieux vaut préciser l'ordre du tri initial :

Code:

[C:C].Sort [C1], xlAscending, Header:=xlYes 'tri
[D:D].Sort [D1], xlAscending, Header:=xlYes 'tri

Je vous laisse corriger le fichier.

A+

job75 · 14 Mars 2014

Re : comparaison de deux colonnes dans excel

Bonjour le fil, le forum,

Finalement dans tous les cas il vaut beaucoup mieux utiliser 2 objets Dictionary :

Code:

Option Explicit
Option Compare Text 'pour ignorer la casse

Sub Résultat()
Dim t, d1 As Object, i&, a, d2 As Object, b, ub&, x$, flag As Boolean, j&
'---1er Dictionary---
t = Range("A2", Range("A" & Rows.Count).End(xlUp)(3))
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare
For i = 1 To UBound(t)
  If t(i, 1) <> "" Then d1(t(i, 1)) = ""
Next
a = d1.keys
'---2ème Dictionary---
t = Range("B2", Range("B" & Rows.Count).End(xlUp)(3))
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare
For i = 1 To UBound(t)
  If t(i, 1) <> "" Then d2(t(i, 1)) = ""
Next
b = d2.keys
ub = UBound(b)
'---Comparaison des 2 Dictionary et suppressions---
For i = 0 To UBound(a)
  x = a(i)
  flag = False
  For j = 0 To ub
    If InStr(b(j), x) Then d2.Remove b(j): flag = True
  Next
  If flag Then
    d1.Remove x
    b = d2.keys
    ub = UBound(b)
  End If
Next
'---résultat trié---
Application.ScreenUpdating = False
Range("C2:C" & Rows.Count).Delete xlUp
If d1.Count Then [C2].Resize(d1.Count) = Application.Transpose(d1.keys)
If d2.Count Then [C2].Offset(d1.Count).Resize(d2.Count) = Application.Transpose(d2.keys)
[C:C].Sort [C1], Header:=xlYes 'tri
End Sub

Cette solution est indispensable sur de grands tableaux.

Fichier (4).

A+

job75 · 14 Mars 2014

Re : comparaison de deux colonnes dans excel

Re,

Je viens de me rendre compte qu'il faut que la 1ère liste soit préalablement triée par ordre décroissant :

Code:

Option Explicit
Option Compare Text 'pour ignorer la casse

Sub Résultat()
Dim t, d1 As Object, i&, a, d2 As Object, b, ub&, x$, j&, flag As Boolean
Application.ScreenUpdating = False
[A2].Resize(Rows.Count - 1).Copy [C2]
[C:C].Sort [C1], xlDescending, Header:=xlYes 'tri décroissant
'---1ère liste sans doublons---
t = Range("C2", Range("C" & Rows.Count).End(xlUp)(3))
Set d1 = CreateObject("Scripting.Dictionary")
d1.CompareMode = vbTextCompare
For i = 1 To UBound(t)
  If t(i, 1) <> "" Then d1(t(i, 1)) = ""
Next
a = d1.keys
'---2ème liste sans doublons---
t = Range("B2", Range("B" & Rows.Count).End(xlUp)(3))
Set d2 = CreateObject("Scripting.Dictionary")
d2.CompareMode = vbTextCompare
For i = 1 To UBound(t)
  If t(i, 1) <> "" Then d2(t(i, 1)) = ""
Next
b = d2.keys
ub = UBound(b)
'---Comparaison des 2 listes et suppressions---
For i = 0 To UBound(a)
  x = a(i)
  For j = 0 To ub
    If InStr(b(j), x) Then d2.Remove b(j): flag = True
  Next
  If flag Then
    flag = False
    d1.Remove x
    b = d2.keys
    ub = UBound(b)
  End If
Next
'---résultat trié en colonne C---
[C2].Resize(Rows.Count - 1).Delete xlUp 'RAZ
If d1.Count Then [C2].Resize(d1.Count) = Application.Transpose(d1.keys)
If d2.Count Then [C2].Offset(d1.Count).Resize(d2.Count) = Application.Transpose(d2.keys)
i = ActiveSheet.UsedRange.Rows.Count 'MAJ du UsedRange
[C:C].Sort [C1], xlAscending, Header:=xlYes 'tri
End Sub

Fichier (5).

Edit : J'ai testé cette macro avec 2 listes identiques a1 a2... a29999 a3000 en colonnes A et B.

Il n'y a bien aucun résultat en colonne C.

Durée d'exécution 9,4 secondes sur Win XP - Excel 2003.

A+

Jacou · 20 Mars 2014

Re : comparaison de deux colonnes dans excel

Bonsoir le forum
ce qui est agréable, c'est que le demandeur initial ne donne aucun signe de vie après les différentes réponses qui ont été données.
ça ne donne pas très envie de se décarcasser pour fournir de l'aide!
Bonne nuit à tous

comparaison de deux colonnes dans excel

gorbachuv

Guest

job75

XLDnaute Barbatruc

Nairolf

XLDnaute Accro

Dugenou

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Jacou

XLDnaute Impliqué

Discussions similaires

comparaison de deux colonnes dans excel

gorbachuv

Guest

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Impliqué

Discussions similaires

Privacy & Transparency

Privacy & Transparency