Comment comparer des valeurs séparées par des virgules dans deux cellules et renvoyer des valeurs en double ou uniques dans Excel ?

Comme le montre la capture d'écran ci-dessous, il y a deux colonnes - Colonne1 et Colonne2, chaque cellule de la colonne contient des nombres séparés par des virgules. Pour comparer les nombres séparés par des virgules dans Column1 avec le contenu des cellules dans la même ligne de Column2 et renvoyer toutes les valeurs en double ou uniques, que pourriez-vous faire ?

Ce didacticiel fournit deux méthodes pour vous aider à accomplir cette tâche.

Comparez les valeurs séparées par des virgules dans deux cellules et renvoyez des valeurs en double ou uniques avec des formules

Cette section fournit deux formules pour aider à comparer les valeurs séparées par des virgules dans deux cellules et renvoyer les valeurs en double ou uniques entre elles.

Notes: Les formules suivantes ne fonctionnent que dans Excel pour 365. Si vous utilisez d'autres versions d'Excel, essayez d'utiliser la méthode VBA ci-dessous.

Prenez les deux colonnes ci-dessus comme exemple, pour comparer les nombres séparés par des virgules dans Column1 avec les nombres séparés par des virgules dans la même ligne de Column2 et renvoyer des valeurs en double ou uniques, veuillez procéder comme suit.

Renvoyer les valeurs en double

1. Sélectionnez une cellule pour générer les nombres en double entre les deux cellules spécifiées avec des nombres séparés par des virgules, dans ce cas, je sélectionne la cellule D2, puis entrez la formule ci-dessous et appuyez sur le Entrer clé. Sélectionnez la cellule de formule et faites-la glisser Poignée de remplissage automatique vers le bas pour obtenir les numéros en double entre les cellules des autres lignes.

=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))

Renvoyer des valeurs uniques

Pour renvoyer les nombres uniques entre les deux cellules spécifiées avec des nombres séparés par des virgules dans la même ligne, la formule suivante peut vous aider.

1. Sélectionnez une cellule pour sortir les nombres uniques, dans ce cas, je sélectionne la cellule E2, puis entrez la formule ci-dessous et appuyez sur le Entrer clé. Sélectionnez la cellule de formule et faites-la glisser Poignée de remplissage automatique vers le bas pour obtenir les numéros uniques entre les cellules des autres lignes.

=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))

Notes:

Comparez deux colonnes avec des valeurs séparées par des virgules et renvoyez des valeurs en double ou uniques avec VBA

La fonction définie par l'utilisateur fournie dans cette section permet de comparer les valeurs séparées par des virgules dans deux cellules spécifiées et de renvoyer les valeurs en double ou les valeurs uniques entre elles. Veuillez procéder comme suit.

Prenez le même exemple que ci-dessus, pour comparer les nombres séparés par des virgules dans Column1 avec les nombres séparés par des virgules dans la même ligne de Column2 et renvoyer des valeurs en double ou uniques, veuillez essayer la fonction définie par l'utilisateur dans cette section.

1. Dans le classeur d'ouverture, appuyez sur la touche autre + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. dans le Microsoft Visual Basic pour applications fenêtre, cliquez sur insérer > Module, et copiez le code VBA suivant dans le Module (Code) fenêtre.

Code VBA : comparer les valeurs séparées par des virgules dans deux cellules et renvoyer des valeurs en double/uniques

Private Function COMPARE(Rng1, Rng2 As Range, Op As Boolean)
'Updated by Extendoffice 20221019
    Dim R1Arr As Variant
    Dim R2Arr As Variant
    Dim Ans1 As String
    Dim Ans2 As String
    Dim Separator As String
    Dim d1 As New Dictionary
    Dim d2 As New Dictionary
    Dim d3 As New Dictionary
    Application.Volatile

    Separator = ", "
    
    R1Arr = Split(Rng1.Value, Separator)
    R2Arr = Split(Rng2.Value, Separator)
    
    Ans1 = ""
    Ans2 = ""
    
    For Each ch In R2Arr
        If Not d2.Exists(ch) Then
            d2.Add ch, "1"
        End If
    Next
    
    If Op Then
        For Each ch In R1Arr
            If d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans1 = Ans1 & ch & Separator
                End If
            End If
        Next
        If Ans1 <> "" Then
            Ans1 = Mid(Ans1, 1, Len(Ans1) - Len(Separator))
        End If
        COMPARE = Ans1
    Else
        For Each ch In R1Arr
            If Not d1.Exists(ch) Then
                d1.Add ch, "1"
            End If
        Next
        
        For Each ch In R1Arr
            If Not d2.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        For Each ch In R2Arr
            If Not d1.Exists(ch) Then
                If Not d3.Exists(ch) Then
                    d3.Add ch, "1"
                    Ans2 = Ans2 & ch & Separator
                End If
            End If
        Next
        If Ans2 <> "" Then
            Ans2 = Mid(Ans2, 1, Len(Ans2) - Len(Separator))
        End If
        COMPARE = Ans2
    End If

End Function

3. Après avoir collé le code dans le Module (Code) fenêtre, allez cliquer Outils > Références ouvrir le Références - VBAProject fenêtre, vérifiez la Exécution de scripts Microsoft boîte, puis cliquez sur le OK .

4. appuie sur le autre + Q touches pour fermer le Microsoft Visual Basic pour applications fenêtre.

5. Vous devez maintenant appliquer deux fonctions séparément pour renvoyer les valeurs en double et uniques à partir de deux cellules de valeur séparées par des virgules.

Renvoyer la valeur en double

Sélectionnez une cellule pour afficher les nombres en double, dans cet exemple, je sélectionne la cellule D2, puis entrez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir les numéros en double entre les cellules A2 et B2.

Sélectionnez la cellule de formule et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les numéros en double entre les cellules des autres lignes.

=COMPARE(A2,B2,TRUE)

Renvoyer des valeurs uniques

Sélectionnez une cellule pour générer les nombres uniques, dans cet exemple, je sélectionne la cellule E2, puis entrez la formule ci-dessous et appuyez sur la touche Entrer clé pour obtenir les numéros uniques entre les cellules A2 et B2.

Sélectionnez la cellule de formule et faites glisser sa poignée de remplissage automatique vers le bas pour obtenir les numéros uniques entre les cellules des autres lignes.

=COMPARE(A2,B2,FALSE)

---

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

• Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
• Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtréeplus
• Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammesplus
• Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
• Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
• Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellulesplus
• Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
• Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDFplus
• Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2021 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.

Lire la suite... Téléchargement Gratuit... Achat... 

---

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

• Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
• Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
• Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!

Lire la suite... Téléchargement Gratuit... Achat...