excel en double paires de valeurs de plusieurs colonnes

Est-il un moyen de trouver doubles paires dans plusieurs colonnes dans Excel?

Par exemple, 

column1       column2  

Smith         Jones       <-- duplicate pair  
Smith         Johnson  
Jones         Smith       <-- duplicate pair  
Jones         Walter
InformationsquelleAutor jrl05k | 2013-05-29
  1. 2

    La façon dont je voudrais le faire serait comme suit:
    (En supposant que vos données commence dans la cellule A1 & B1)

    • En C1, le mettre dans la formule: =A1&"|"&B1
    • En D1, les mettre dans la formule: =B1&"|"&A1
    • Dans E1, mettre dans la formule: =MATCH(C1,$D$1:$D$500,0)
    • Faites glisser tous les vers le bas pour votre jeu de données (et de changer le $500 dans votre formule finale si nécessaire)
    • Tout endroit où la colonne E est un nombre, cela signifie que les données sont copiées - Vous pourriez même filtre pour tout ce qui n'est pas #N/A et il vous indique où dans la liste le double de match.

    Espérons que cette aide!!

    • Ne serait pas logique de dire que la paire ("ABC", "DEF") correspond à ("AB", "CDEF") alors...ou ("ABC", "DEF") correspond à ("CDEF", "AB")?
    • Oui, Vous avez raison, j'ai mis à jour ma réponse à corriger ça... Bonne pêche!!
    InformationsquelleAutor John Bustos
  2. 1

    Si vous ne voulez qu'une seule colonne pour indiquer un duplicata de ce type, vous pouvez utiliser cette formule:

    =SUMPRODUCT((CONCATENATE($A$1:$A$500,"^",$B$1:$B$500)=CONCATENATE(B1,"^",A1))*1)

    Cela vous donnera le nombre de doublons de le type que vous avez dans votre tableau. Vous pouvez appliquer un filtre sur toutes les valeurs qui sont non nuls. Il suffit de changer la gamme de $A$1:$A$500 et $B$1:$B$500 en cas de besoin. Il dépendra de la taille de votre table.

    Ou si, par quelque changement que vous voulez utiliser la mise en forme conditionnelle, vous pouvez sélectionner les colonnes A et B, cliquez sur "mise en forme Conditionnelle', 'Nouvelle Règle', 'Utiliser la formule pour déterminer pour quelles cellules le format', collez-le dans la formule:

    =SUMPRODUCT((CONCATENATE($A$1:$A$500,"^",$B$1:$B$500)=CONCATENATE(B1,"^",A1))*1)>0

    puis sélectionnez une mise en forme à partir du bouton "Format..." (je l'habitude d'aller pour "Remplir" et sélectionnez jaune) qui mettra en évidence les doublons dans la colonne B.

    InformationsquelleAutor Jerry
  3. 1

    J'aimerais tout d'abord créer une colonne en concaténant le tri des valeurs de la Colonne 1 et 2 (et un espace dans le milieu). Alors que je venais de rechercher les doublons dans cette colonne. La suite devrait le faire:

    1. En C1, =SI(A1<=B1,A1 & " | "& B1,B1 & " | " & A1)
    2. En D1, =NB.SI($C$1:$C$999,C1)>1

    Alors il suffit de copier les cellules C1, et D1, tout le chemin en bas de la feuille de calcul. La colonne D doit contenir TRUE pour les doubles paires.

    Edit: Changé le délimiteur (toujours pas infaillible, mais en mieux)

    • Je voudrais utiliser un autre séparateur à la place d'un espace. Alors que les cas comme "John, Doe Smith" et "Smith, John Doe" est rare, il n'est pas impossible et je pense qu'il est préférable d'utiliser un personnage on ne trouve pas dans les noms 🙂
    • Bon appel. Je n'ai jamais pensé à plusieurs noms. Je suppose que la solution "idéale" pour arbitraire données serait de coder chaque nom en quelque sorte, d'échapper à un caractère spécial, puis utiliser ce caractère spécial comme un séparateur.
    InformationsquelleAutor Gerrat