Trouver toutes les colonnes avec des valeurs identiques

Bonjour,

Je ne maîtrise pas bien le sujet, car je n'utilise pas souvent Excel/Google Sheets.
Cependant je suis à peu près certaine qu'il y a une réponse à ma question.

J'ai une feuille avec des valeurs dans mes colonnes.
Je souhaite faire ressortir* les colonnes qui ont les mêmes valeurs, peu importe leur ligne.

J'ai essayé de faire un exemple (en PJ) pour plus de clarté:
Mes colonnes A et E ont exactement les mêmes valeurs (même si elles ne sont pas rangées dans le même ordre): ça m'intéresse.
Ma colonne C a des valeurs communes à mes colonnes A et E mais il en manque: donc ça ne m'intéresse pas.
Mes colonnes D et F ont exactement les mêmes valeurs (dans un ordre différent) : ça m'intéresse.

* L'idée n'est pas forcément de faire ressortir ces colonnes, aux valeurs identiques, avec des couleurs.
Ca pourrait être une simple cellule qui me dirait: "colonne A = colonne E" ; "colonne D = Colonne F"

J'ai une feuille avec pas moins de 200 colonnes et je dois trouver celles avec des valeurs identiques.

Quelqu'un aurait une idée de comment procéder?
Je n'imagine pas le temps que cela me prendrait de traiter ces données manuellement

Merci d'avance pour vos réponses!

Bon dimanche à tous.

Bonjour,

veux-tu une réponse valable pour excel ou pour google sheet ?

Bonjour,

Une réponse valable pour Google Sheets ca serait mieux

Malheureusement ce sera super complexe, par manque d'expérience et de contributeurs. Je demandais cela pour savoir si tu étais opposée à un réponse excel qui est plus simple ! Je regarderai ce soir sous cet angle. A moins que d'autres aient pris le relais.

Un premier jet

Sub comparer()
Dim Data1, Data2, dico As Object, i, j, k
Set dico = CreateObject("Scripting.Dictionary")

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column - 1
    For j = i + 1 To Cells(1, Columns.Count).End(xlToLeft).Column
        Data1 = Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Value
        Data2 = Range(Cells(1, j), Cells(Cells(Rows.Count, j).End(xlUp).Row, j)).Value
        dico.RemoveAll
        For k = LBound(Data2) To UBound(Data2)
            dico(Data2(k, 1)) = ""
        Next
        flag = True
        For k = LBound(Data1) To UBound(Data1)
            If Not dico.exists(Data1(k, 1)) Then flag = False
        Next
        If flag Then MsgBox "Les colonnes " & i & " et " & j & " correspondent !"
    Next
Next

End Sub

Reste à mettre des couleurs ... mais combien pour qu'il n'y ait pas de chevauchement ?

Maintenant, un peu de réflexion ...

1. il se peut qu'il y ait des triples correspondances, voire plus : colonne 2 / colonne 25 / colonne 67 ... je vais voir comment présenter un tableau de synthèse (qui devrait être une matrice carrée dont seul un triangle est renseigné
2. 200 colonnes, dis moi si déjà le fait de faire tourner cette macro en l'état est long, car je peux optimiser de 3 façons
   1. créer une seule fois un dictionnaire par colonne
   2. tout se suite comparer le nombre d'éléments de chaque colonne
   3. stopper la comparaison dès qu'un élément est absent

avec les 2 optimisations 2 et 3

edit : fichier supprimé au profit du suivant

Bonjour à tous,

Une piste pour Google Sheets en passant (qui applique une couleur unique aux doublons) :

La formule à étirer vers la droite, avec SORT et JOIN pour trier et concaténer (pour appliquer ensuite une MFC plus facilement) :

=JOIN(CAR(10);SORT(A1:A4))

La MFC de détection des doublons :

=NB.SI($A6:$F6;A6)>1

Cordialement,

avec une grille de résultats

merci Sébastien, je vais pouvoir progresser sur google sheet du coup

as-tu la même chose en excel ?

Steelson a écrit :

as-tu la même chose en excel ?

Si je devais rapidement le faire sur Excel, je le ferais avec la fonction JOINDRE_NON_VIDE_TRI du pack qui convient parfaitement pour reproduire ce cas (et avec la même MFC que pour Google Sheets) :

=JOINDRE_NON_VIDE_TRI(A1:A4;CAR(10))

Alors là, bravo !

Déjà un grand merci pour votre aide précieuse.

Pour répondre @Steelson:
A la maison je n'ai que Google Sheets, mais au boulot j'ai Excel (je ne peux cependant pas te dire quelle version, mais je checkerai demain).
Je ne sais même pas combien j'ai de colonnes exactement. Mon estimation est vraiment "à la louche". Je m'y mets de suite pour voir combien de colonnes j'ai exactement.
Si on passe par Google Sheets, je me fiche un peu de combien de temps ça prend. Je peux lancer la macro un soir et avoir mes réponses que le lendemain ça ne me pose pas de problème particulier ( A moins qu'on parte sur une macro qui tourne sur 3 jours....) . Par Excel ca va être plus compliqué, le PC travail n'est pas très performant et surtout j'en ai besoin pour mes autres tâches.
Je pense que l'idée de comparer le nombre d'éléments de chaque colonne n'est pas mal du tout.
Est-ce que dans ce cas on pourrait comparer uniquement les colonnes qui ont le même nombre d'éléments?

Si plus simple par Excel, why not! Je suis ouverte à toute suggestion qui me permettra de résoudre ceci de la manière la plus simple.

J'ai essayé par la mise en forme conditionnelle: Dire que si les valeurs de mes colonnes A, C, D et F sont identiques à la colonne B alors mettre en "Bleu".
Je me suis vite rendu compte que ça voulait dire que je devais créer une nouvelle formule pour chaque colonne. Imo pas très opti comme solution.

La solution de Sébastien me permettrait au moins de savoir quelle colonne n'a pas de doublon. Ca pourrait déjà réduire mon nombre de colonnes à traiter pour les doublons.

Du coup:

Je me suis penchée sur la méthode de Sébastien et je crois que ça peut résoudre mon problème.
J'ai quelques soucis avec la MFC mais je vais bosser sur le sujet et voir ce que ca donne.

Merci à vous deux, je garde le sujet ouvert encore un peu, au cas où je ne sache pas résoudre ce problème de MFC et je clôture dès que c'est bon.

Un grand merci à vous pour votre aide! C'est très aimable.

Alors, là, je me suis juste fait plaisir !

Option Explicit

Sub comparer()
Dim Data1, Data2, dico As Object, dejafait As Object, i%, j%, k%, flag As Boolean, rng1 As Range, rng2 As Range, cle, debut As Date
Set dico = CreateObject("Scripting.Dictionary")
Set dejafait = CreateObject("Scripting.Dictionary")

debut = Now
raz

For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column - 1
    If Not dejafait.exists(i) Then ' correspondance testée
        Set rng1 = Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i))
        Data1 = Range(Cells(1, i), Cells(Cells(Rows.Count, i).End(xlUp).Row, i)).Value
        dico.RemoveAll
        For k = LBound(Data1) To UBound(Data1)
            dico(Data1(k, 1)) = ""
        Next
        For j = i + 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            Set rng2 = Range(Cells(1, j), Cells(Cells(Rows.Count, j).End(xlUp).Row, j))
            Data2 = Range(Cells(1, j), Cells(Cells(Rows.Count, j).End(xlUp).Row, j)).Value
            flag = True
            If UBound(Data2) = UBound(Data1) Then
                For k = LBound(Data2) To UBound(Data2)
                    If Not dico.exists(Data2(k, 1)) Then flag = False: Exit For
                Next
                If flag Then
                    Sheets("correspondances").Cells(i + 1, j + 1) = "ok"
                    If Not dejafait.exists(i) Then dejafait(i) = i: colorier rng1, i
                    dejafait(j) = i
                    colorier rng2, i
                End If
            End If
        Next
    Else ' correspondances déduites
        For Each cle In dejafait
            If dejafait(cle) = dejafait(i) And cle > i Then Sheets("correspondances").Cells(i + 1, cle + 1) = "(x)"
        Next
    End If
Next

With Sheets("groupes").ListObjects(1)
    For Each cle In dejafait
        .ListRows.Add
        .DataBodyRange.Cells(.ListRows.Count, 1) = dejafait(cle)
        .DataBodyRange.Cells(.ListRows.Count, 2) = cle
    Next
End With
Sheets("synthèse groupes").PivotTables("Tableau croisé dynamique1").PivotCache.Refresh

MsgBox "Terminé en " & Format(Now - debut, "hh:mm:ss")

End Sub

Sub colorier(plage As Range, n As Integer)
Dim p, c
c = (n Mod 54) + 3 ' on évite le 2 (blanc)
p = Array(2, 1, 2, 1, 2, 1, 1, 1, 2, 2, 2, 2, 2, 2, 1, 2, 1, 2, 1, 1, 2, 1, 2, 1, 2, 1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
    plage.Interior.ColorIndex = c
    plage.Font.ColorIndex = p(c - 1)
End Sub

Sub raz()
Dim i%, j%
    With Sheets("groupes").ListObjects(1)
        If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
    End With
    Sheets("données").Select
    Cells.Interior.Pattern = xlNone
    Cells.Font.ColorIndex = xlAutomatic
    With Sheets("correspondances")
        .Cells.ClearContents
        .Cells(2, 1) = 1
        .Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column + 1) = Cells(1, Columns.Count).End(xlToLeft).Column
        For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column - 1
            Sheets("correspondances").Cells(1, i + 1) = i
            For j = i + 1 To Cells(1, Columns.Count).End(xlToLeft).Column
                .Cells(j + 1, 1) = j
            Next
        Next
    End With
End Sub