Recherche d'égalités dans 2 colonnes différentes

**MrcRafale** · 15/06/2016, 17h13

Bonjour,

J'ai dans une même feuille 2 colonne, dans lesquelles j'ai des noms de personnes. Certains noms sont dans les deux colonnes, mais d'autres que dans une seule.
Je voudrais pouvoir afficher dans une troisième colonne tous les noms des personnes qui ne se trouvent que dans l'une des deux colonnes mentionnées précédemment.
J'ai essayé différentes boucles (Do while, If, For...), le programme n'affiche jamais ce que je veux.

Comment faire?

Merci

**Menhir** · 15/06/2016, 17h49

A la main, ça ne serait pas très compliqué à faire.

En supposant que tes colonnes sont A et B et que tes données vont de la ligne 1 à la ligne 100.

Tu mets en C1 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NB.SI($B$1:$B$100;$A1)=0;$A1;"")

En D1 tu mets la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NB.SI($A$1:$A$100;$B1)=0;$B1;"")

Tu copies C1: D1 dans C1: D100.

Tu sélectionnes C1: D100 > Ctrl+C > Icone Coller > Coller Valeur.

Ensuite, tu copies la colonne D à la fin de la colonne C et tu fais dessus un tri pour éliminer les cellules vides.

C'est plus long à décrire qu'à faire.

Si tu veux le faire en VBA, il y a plusieurs méthodes possible.
Perso, celle que j'appliquerai, c'est de commencer à copier les deux colonnes A et B en C et D.
Ensuite, je lancerai une boucle For to en commençant par la fin (Step -1).
Pour chaque cellule de la colonne C, tu lances une recherche de sa valeur sur la colonne B avec un Find. Si le Find ne renvoie pas Nothing, tu supprimes la cellule avec un Delete(xlShiftUp).
Tu fais la même chose avec sa voisine en colonne D et un Find sur A.

Après la boucle, tu déplaces le contenu de D à la fin de C avec un Cut et un Copy.

**MrcRafale** · 15/06/2016, 18h04

Bonjour Mehnir,

Merci, j'avais pas du tout pensé à la fonction "find"!! Je vais essayer avec ça.
La solution en entrant directement la formule dans la cellule ne me parait pas judicieuse dans mon cas d'application, puisque mes listes de noms sont variables... Du coup ça ferait bizarre non? Et puis une mauvaise manip et on perd tout. Mais je vais essayer les deux quand même.

Je reviens vers vous demain avec une conclusion ou une autre demande

**RyuAutodidacte** · 16/06/2016, 23h00

Bonsoir,
pour mon plaisir, mon entrainement et donner un peu d'aide
j'ai fais un petit code (à adapter), les pointages Classeur.feuille ne sont pas fait => à faire
noms en colonne A et B - résultat en colonne D
je suis parti du principe que l'on ne voulait pas de doublons dans les noms
Utilisation de variable tableau + collection

PS : Merci aux cadors de montrer une autre possibilité à des fins d'amélioration

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub test2()
Dim VA, V, DL1&, DL2&, DL&, Noms As New Collection
 
Application.ScreenUpdating = False
    DL1 = Range("A" & Rows.Count).End(xlUp).Row
    DL2 = Range("B" & Rows.Count).End(xlUp).Row
    DL = DL1 + DL2
 
    VA = Application.Transpose(Range("A1").CurrentRegion.Columns(1))
    ReDim Preserve VA(1 To DL)
 
    For x& = 1 To DL2
        VA(DL1 + x) = Range("B" & x).Value
    Next
 
    i& = 1
    On Error Resume Next
    For Each V In VA
        Noms.Add VA(i), CStr(VA(i))
        If Err.Number <> 0 Then
            Err.Clear
        End If
        i = i + 1
    Next
    For i = 1 To Noms.Count
    Range("D" & i) = Noms.Item(i)
    Next
    On Error GoTo 0
Application.ScreenUpdating = True    
    Set Noms = Nothing
 
End Sub

Tiens tiens VA, V me dit qq ch, petit

à qui sait

**Marc-L** · 24/06/2016, 17h30

Bonjour !

Envoyé par RyuAutodidacte

montrer une autre possibilité à des fins d'amélioration

Tiens tiens VA, V me dit qq ch, petit

à qui sait

En utilisant le B-A-BA d'Excel, une formule de calculs (comme justement souligné par Menhir),
source sans titre en colonnes A & B, colonne C vide, résultat en colonne D :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub Demo1()
         Const F = "TRANSPOSE(IF(#1>"""",IF(COUNTIF(#2,#1)=0,#1)))", J = ""","""
    With Cells(1).CurrentRegion.Columns
        S$ = Join(Filter(Evaluate(Replace$(Replace$(F, "#1", .Item(1).Address), "#2", .Item(2).Address)), False, False), J)
        T$ = Join(Filter(Evaluate(Replace$(Replace$(F, "#1", .Item(2).Address), "#2", .Item(1).Address)), False, False), J)
    End With
        If S > "" Then S = """" & S & """"
        If T > "" Then T = """" & T & """"
        Cells(4).CurrentRegion.Clear
        VA = Evaluate("TRANSPOSE({" & S & IIf(S > "" And T > "", ",", "") & T & "})")
        If Not IsError(VA) Then Cells(4).Resize(UBound(VA)).Value = VA
End Sub

_________________________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

_________________________________________________________________________________________________________
Je suis Paris, Charlie, Bruxelles, …

**RyuAutodidacte** · 24/06/2016, 18h04

Re,
Merci Marc

, n'étant pas habitué à procéder de la sorte, je regarderai de plus près (ça me rappelle un peu le principe avec "Filter" avec "les doublons que je ne pouvais pas tester, d'ailleurs il faut que je le teste celui la)

**Marc-L** · 24/06/2016, 18h50

Ah oui c'est vrai, la version MAC 2014 !?

En cas de souci tu pourras tester en émulation Windows …

**RyuAutodidacte** · 24/06/2016, 22h21

Re,
je reviens non pas d'une dégustation de bière et de champagne malheureusement

... mais de la fête de l'école

Envoyé par Marc-L

Ah oui c'est vrai, la version MAC 2014 !?

la dernière version Mac ok pour le VBA est 2011; celle de 2016 est une catastrophe niveau VBA (vu en aidant qq un du forum et vu sur le net)

Envoyé par Marc-L

En cas de souci tu pourras tester en émulation Windows …

En fait, ce n'est pas une émulation mais vraiment Windows 10 pro d'installer avec excel 2010, ...

j'ai un double boot maintenant

Edit : Filter bugguer sur Excel Mac 2011

**Marc-L** · 24/06/2016, 23h41

Sinon il y a bien l'utilisation de l'objet Dictionary mais uniquement sous Windows,
pour MAC & PC tu as déjà exploré les collections,
alors voici un p'tit algo classique sur PC comme sur MAC :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Demo2()
    Application.ScreenUpdating = False
    Cells(4).CurrentRegion.Clear
        With Cells(1).CurrentRegion.Columns
            VA = .Item(1).Value
            VB = .Item(2).Value
        End With
        For Each V In VA
            If V > "" Then
                    W = Application.Match(V, VB, 0)
                If IsError(W) Then
                    L& = L& + 1
                    Cells(L, 4).Value = V
                Else
                    VB(W, 1) = ""
                End If
            End If
        Next
        For Each V In VB
            If V > "" Then L = L + 1: Cells(L, 4).Value = V
        Next
    Application.ScreenUpdating = True
End Sub

Optimisation de Demo1 en s'affranchissant de la transposition finale
(comparer les lignes de code n°2 & 10 entre les deux procédures) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub Demo1b()
         Const F = "TRANSPOSE(IF(#1>"""",IF(COUNTIF(#2,#1)=0,#1)))", J = """;"""
    With Cells(1).CurrentRegion.Columns
        S$ = Join(Filter(Evaluate(Replace$(Replace$(F, "#1", .Item(1).Address), "#2", .Item(2).Address)), False, False), J)
        T$ = Join(Filter(Evaluate(Replace$(Replace$(F, "#1", .Item(2).Address), "#2", .Item(1).Address)), False, False), J)
    End With
        If S > "" Then S = """" & S & """"
        If T > "" Then T = """" & T & """"
        Cells(4).CurrentRegion.Clear
        VA = Evaluate("{" & S & IIf(S > "" And T > "", ";", "") & T & "}")
        If Not IsError(VA) Then Cells(4).Resize(UBound(VA)).Value = VA
End Sub

_________________________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

**RyuAutodidacte** · 29/06/2016, 12h46

Bonjour Marc,
J'essaie désespérément de comprendre demo1b ce qui n'est pas une moindre affaire, mon esprit n'étant pas encore habitué à ce type de code (j'ai besoin de visualiser ce qui se passe).
De ce que je vois, on utilise 2 constantes : la formule et ";" (sur demo1 "," - fallait le voir

en ligne 2)
Je pense que ce qui m'empêche de visualiser ce sont les #. Dans Excel les # sont utilisés pour les formats des nombres.
Donc je suppose que les # sont remplacés par un chiffre avec Evaluate !? Est ce cela?
Du coup je ne visualise pas la transformation avec Join Filter Evaluate Replace Replace, et me pose des questions sur le type de formule qui je suppose n'est pas en formula.local.

Un petit coup de pouce serait le bienvenue pour éclairer ma lanterne stp.

PS : sur le test que j'ai fait, j'ai un résultat différent entre demo1-demo1b par rapport à demo2
Je ne pourrais que m'y remettre ce soir (trop de taf et pas d'accès à un PC)

Merci d'avance

**Marc-L** · 29/06/2016, 12h56

Soit la constante F contenant une formule - matricielle - dans laquelle
#1 représente l'adresse des cellules d'une première colonne et #2 celle d'une seconde colonne.

La méthode de remplacement m'évite des concaténations d'adresses de cellules à rallonge
et surtout grâce à la constante de visualiser aisément la formule utilisée …

De mon côté je n'ai eu aucune différence entre les procédures,
en espérant ne pas être encore tombé dans un piège MAC !

**RyuAutodidacte** · 29/06/2016, 13h28

Re,
Malheureusement ce n'est pas un piège Mac ! (Fait sur Windows et Excel 2010)
J'ai utilisé des noms en colonne A et B dont certains pouvez se répéter dans une même colonne ou exister sur la colonne d'en face.
Je remettrai les valeurs de A et B ce soir avec les résultats des démos
Merci c'est plus clair pour l'histoire des #

**Marc-L** · 29/06/2016, 19h20

Dans ce cas peut-être mes données de tests n'étaient pas assez conséquentes,
je suis certainement passé au travers des doublons multiples en relisant mon code,
merci de joindre ton classeur de données sans code au format .xlsx …

Edit : dans mon fichier test chaque colonne était sans doublon …

**RyuAutodidacte** · 29/06/2016, 20h19

Re,
Voilà le fichier

Gestion TAB.xlsx
les tests et résultats dont je parlais sont en feuil2
Merci

**Marc-L** · 29/06/2016, 20h38

C'est bien cela (j'ai édité entre temps mon précédent message) : les données en doublon au sein d'une même colonne !
J'ai supposé chaque colonne sans doublon, d'où l'intérêt d'une présentation claire & exhaustive …

Dans ce cas, l'utilisation d'un double dictionnaire est évidente
ou côté MAC d'une double collection (ou une classe dictionnaire utilisant une collection

) …
Ou encore valable pour Windows comme pour MAC l'utilisation de colonnes intermédiaires
alimentées par un filtre avancé avec l'option sans doublon …

Si possibilité de doublons au sein d'une même colonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Sub Demo2b()
    Application.ScreenUpdating = False
    Cells(4).CurrentRegion.Clear
        With Cells(1).CurrentRegion.Columns
            VA = .Item(1).Value
            VB = .Item(2).Value
        End With
        For R& = 1 To UBound(VA)
            If VA(R, 1) > "" Then
                    V = VA(R, 1)
                    VA(R, 1) = ""
                    W = Application.Match(V, VB, 0)
                If IsError(W) Then
                    If IsError(Application.Match(V, Cells(4).CurrentRegion, 0)) Then
                        L& = L& + 1
                        Cells(L, 4).Value = V
                    End If
                Else
                    Do
                        VB(W, 1) = ""
                        W = Application.Match(V, VB, 0)
                    Loop Until IsError(W)
                End If
                Do
                    W = Application.Match(V, VA, 0)
                    If IsNumeric(W) Then VA(W, 1) = ""
                Loop Until IsError(W)
            End If
        Next
        For Each V In VB
            If V > "" Then
                If IsError(Application.Match(V, Cells(4).CurrentRegion, 0)) Then
                    L = L + 1
                    Cells(L, 4).Value = V
                End If
            End If
        Next
    Application.ScreenUpdating = True
End Sub

Autre manière en conservant l'algorithme de Demo2
est de retirer d'abord les doublons par colonne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub Demo2a()
    Application.ScreenUpdating = False
    Cells(4).CurrentRegion.Clear
        With Cells(1).CurrentRegion.Columns
            For C% = 1 To 2
                 V = .Item(C).Value
                 ReDim VB(1 To UBound(V))
            For Each W In V
                If W > "" Then If IsError(Application.Match(W, VB, 0)) Then L& = L& + 1: VB(L) = W
            Next
                 ReDim Preserve VB(1 To L)
                 If C = 1 Then VA = VB
                 L = 0
            Next
        End With
        For Each V In VA
            If V > "" Then
                    W = Application.Match(V, VB, 0)
                If IsError(W) Then
                    L = L + 1
                    Cells(L, 4).Value = V
                Else
                    VB(W) = ""
                End If
            End If
        Next
        For Each V In VB
            If V > "" Then L = L + 1: Cells(L, 4).Value = V
        Next
    Application.ScreenUpdating = True
End Sub

Je préfère Demo2a à Demo2b, mais bon les goûts, les couleurs …
C'est juste d'un point de vue algorithmique car, plus il y aura d'éléments à traiter
plus un dictionnaire (ou une collection sur MAC) s'avérera bien plus efficace !

Ryu, avec ton classeur joint et ta procédure test2 du post #4 il y a un intrus dans le résultat …

**RyuAutodidacte** · 29/06/2016, 23h32

Re,
j'ai testé les 2 Démo2 (a,b) et le résultat est sans appel

Ryu, avec ton classeur joint et ta procédure test2 du post #4 il y a un intrus dans le résultat …

Tu as tout à fait raison

Je me suis rendu compte que j'avais pas compris la petite subtilité dans la demande initial, c'est-à-dire, que si 1 nom se retrouve dans les 2 colonnes,
celui-ci est exclu alors du résultat. Ce que je n'ai pas fait dans ma réponse au post #4, mon résultat donne tous les noms sans doublons.
Ceci à vraiment son importance selon ce que l'on veut faire.

Merci pour les démonstrations

Edit :

(ou une classe dictionnaire utilisant une collection )

ne t'iquiète pas je ne l'ai pas oublié celui-ci

Edit 2

Autre manière en conservant l'algorithme de Demo2
est de retirer d'abord les doublons par colonne :
....
Je préfère Demo2a à Demo2b, ...

Je suis aussi d'accord avec toi, c'est plus logique de retirer les doublons avant

**Marc-L** · 30/06/2016, 01h10

Version dictionnaire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub Demo3()
        Dim VDic(1 To 2), C%
    With Cells(1).CurrentRegion.Columns
        For C = 1 To 2
                Set VDic(C) = CreateObject("Scripting.Dictionary")
            For Each V In .Item(C).Value
                If V > "" Then VDic(C).Item(V) = ""
            Next
        Next
    End With
    For Each V In VDic(1)
        If VDic(2).Exists(V) Then VDic(1).Remove V: VDic(2).Remove V
    Next
        Cells(4).CurrentRegion.Clear
        If VDic(1).Count Then Cells(4).Resize(VDic(1).Count).Value = Application.Transpose(VDic(1).Keys)
        If VDic(2).Count Then Cells(VDic(1).Count + 1, 4).Resize(VDic(2).Count).Value = Application.Transpose(VDic(2).Keys)
        VDic(1).RemoveAll:  Set VDic(1) = Nothing
        VDic(2).RemoveAll:  Set VDic(2) = Nothing
End Sub

Version collection:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub Demo4()
            Dim COL(1 To 2) As New Collection, C&, R&
    With Cells(1).CurrentRegion.Columns
            On Error Resume Next
        For C = 1 To 2
            For Each V In .Item(C).Value
                If V > "" Then COL(C).Add V, V
            Next
        Next
    End With
    For Each V In COL(1)
        Err.Clear
        V = COL(2)(V)
        If Err.Number = 0 Then COL(1).Remove V: COL(2).Remove V
    Next
            On Error GoTo 0
            Cells(4).CurrentRegion.Clear
       C = COL(1).Count + COL(2).Count
    If C Then
            ReDim VA$(1 To C, 0)
        For C = 1 To 2
            For Each V In COL(C):  R = R + 1:  VA(R, 0) = V:  Next
        Next
            Cells(4).Resize(R).Value = VA
    End If
End Sub

**RyuAutodidacte** · 30/06/2016, 09h19

Bonjour Marc,
Merci pour la version dictionnaire (Surtout à une heure si tardive)

, je la testerai ce soir.
Je vais essayer de trouver un code différent pour rectifier mon erreur du post #4.
Ryu

Edit : merci aussi pour la version collection (dsl je n'avais pas les yeux en face des trous ce matin

)

**RyuAutodidacte** · 01/07/2016, 00h45

Bonsoir Marc,
j'ai testé la version Dictionnaire et Collection et regardé en détail le déroulement du code :
la façon de coder dont je ne connaissais pas le principe avec une collection (même principe avec le dictionnaire) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Dim Col(1 To 2) As New Collection
'...
'------------------------------------------------
Dim VDic(1 To 2)
'...

est tout simplement topissime

Du coup tu as fait tellement de version que ça va être difficile de trouver un autre principe de codage différent mais on va essayer (là je ne promets rien

)

il y a 3 mots qui me viennent à l’esprit pour tes codes

Logique - Concis - Efficacité =>

+++

Encore

et surtout grand merci pour tes Démos

**Marc-L** · 01/07/2016, 01h06

Merci Ryu, connaissant ta soif d'apprendre …

Pour éliminer les doublons il y a encore le filtre avancé - s'il y a une ligne de titres - avec des colonnes temporaires.

Tout est une question de contexte ou de contraintes imposées …
J'ai une préférence pour la formule - si les colonnes sont sans doublon - mais avec un grand volume de données
la célérité de l'objet Dictionary peut prévaloir et côté MAC, ne pas oublier la Collection.

Si quelqu'un me demande que choisir, je conseille la solution avec laquelle il est le plus à l'aise afin de la maintenir …

Envoyé par MrcRafale

J'ai essayé différentes boucles (Do while, If, For...), le programme n'affiche jamais ce que je veux.

Le programme accomplit juste les instructions codées …
Donc pour ceux ayant des problèmes d'algorithmie et même pour des doublons par colonne :

Envoyé par Menhir

A la main, ça ne serait pas très compliqué à faire.

Envoyé par Marc-L

En utilisant le B-A-BA d'Excel, une formule de calculs (comme justement souligné par Menhir),
source sans titre en colonnes A & B, colonne C vide, résultat en colonne D

Ryu, à partir de ton classeur joint, les critères sont cellule A1 non vide,
la première occurrence du nom dans la colonne et nom n'existant pas dans la colonne B,
voici donc la formule de la cellule D1 avec une version française d'Excel :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI($A1>"";SI(NB.SI(INDIRECT("$A$1:$A$"&LIGNE());$A1)=1;SI(NB.SI($B$1:$B$6;$A1)=0;$A1;"");"");"")

La recopier vers le bas jusqu'à la cellule D6 …

Pour la colonne B, il suffit de commencer en D7 puis d'adapter la formule en inversant les références
des colonnes A & B et en insérant une soustraction de 6 au numéro de ligne de la cellule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub Demo1c()
    Cells(4).CurrentRegion.Clear
    R& = Cells(1).CurrentRegion.Rows.Count
    F$ = "=SI($A1>"""";SI(NB.SI(INDIRECT(""$A$1:$A$""&LIGNE());$A1)=1;SI(NB.SI($#$1:$#$" & R & ";$A1)=0;$A1;"""");"""");"""")"
    Cells(4).Resize(R).FormulaLocal = Replace(F, "#", "B")
    Cells(R + 1, 4).Resize(R).FormulaLocal = Replace(Replace(Replace(F, "$A", "$B"), "))", ")-" & R & ")"), "#", "A")
      With Cells(4).Resize(R * 2)
          .Formula = .Value
          .Sort .Cells(1), xlAscending, Header:=xlNo
      End With
End Sub