¿Cómo comparar valores separados por comas en dos celdas y devolver valores duplicados o únicos en Excel?

Como se muestra en la captura de pantalla a continuación, hay dos columnas: Columna1 y Columna2, cada celda en la columna contiene números separados por comas. Para comparar los números separados por comas en la Columna1 con el contenido de las celdas en la misma fila de la Columna2 y devolver todos los valores duplicados o únicos, ¿qué podrías hacer?

Este tutorial proporciona dos métodos para ayudarte a realizar esta tarea.

Comparar valores separados por comas en dos celdas y devolver valores duplicados o únicos con fórmulas

Esta sección proporciona dos fórmulas para ayudarte a comparar los valores separados por comas en dos celdas y devolver los valores duplicados o únicos entre ellos.

Nota: Las siguientes fórmulas solo funcionan en Excel para 365. Si estás utilizando otras versiones de Excel, intenta usar el método VBA que se describe a continuación.

Tomando las dos columnas anteriores como ejemplo, para comparar los números separados por comas en la Columna1 con los números separados por comas en la misma fila de la Columna2 y devolver valores duplicados o únicos, sigue los pasos a continuación.

Devolver valores duplicados

1. Selecciona una celda para mostrar los números duplicados entre las dos celdas especificadas con números separados por comas; en este caso, selecciono la celda D2, luego ingresa la siguiente fórmula y presiona la tecla Enter. Selecciona la celda con la fórmula y arrastra su controlador de relleno automático hacia abajo para obtener los números duplicados entre las celdas en las demás filas.

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

Devolver valores únicos

Para devolver los números únicos entre las dos celdas especificadas con números separados por comas en la misma fila, la siguiente fórmula puede ayudarte.

1. Selecciona una celda para mostrar los números únicos; en este caso, selecciono la celda E2, luego ingresa la siguiente fórmula y presiona la tecla Enter. Selecciona la celda con la fórmula y arrastra su controlador de relleno automático hacia abajo para obtener los números únicos entre las celdas en las demás filas.

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

Notas:

Descubre la Magia de Excel con Kutools AI

• Ejecución Inteligente: Realiza operaciones en celdas, analiza datos y crea gráficos, todo impulsado por comandos simples.
• Fórmulas Personalizadas: Genera fórmulas adaptadas para optimizar tus flujos de trabajo.
• Codificación VBA: Escribe e implementa código VBA sin esfuerzo.
• Interpretación de Fórmulas: Comprende fórmulas complejas con facilidad.
• Traducción de Texto: Supera las barreras del idioma dentro de tus hojas de cálculo.

Potencia tus capacidades de Excel con herramientas impulsadas por IA. ¡Descarga Ahora y experimenta una eficiencia como nunca antes!

Comparar dos columnas con valores separados por comas y devolver valores duplicados o únicos con VBA

La función definida por el usuario proporcionada en esta sección ayuda a comparar los valores separados por comas en dos celdas especificadas y devolver los valores duplicados o únicos entre ellos. Sigue los pasos a continuación.

Tomando el mismo ejemplo de arriba, para comparar los números separados por comas en la Columna1 con los números separados por comas en la misma fila de la Columna2 y devolver valores duplicados o únicos, prueba la función definida por el usuario en esta sección.

1. En el libro abierto, presiona las teclas Alt + F11 para abrir la ventana Microsoft Visual Basic para Aplicaciones.

2. En la ventana Microsoft Visual Basic para Aplicaciones, haz clic en Insertar > Módulo, y copia el siguiente código VBA en la ventana Módulo (Código).

Código VBA: Comparar valores separados por comas en dos celdas y devolver valores duplicados/únicos

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. Después de pegar el código en la ventana Módulo (Código), ve a hacer clic en Herramientas > Referencias para abrir la ventana Referencias – VBAProject, marca la casilla Microsoft Scripting Runtime y luego haz clic en el botón Aceptar.

4. Presiona las teclas Alt + Q para cerrar la ventana Microsoft Visual Basic para Aplicaciones.

5. Ahora necesitas aplicar dos funciones por separado para devolver los valores duplicados y únicos de dos celdas con valores separados por comas.

Devolver valor duplicado

Selecciona una celda para mostrar los números duplicados; en este ejemplo, selecciono la celda D2, luego ingresa la siguiente fórmula y presiona la tecla Enter para obtener los números duplicados entre las celdas A2 y B2.

Selecciona la celda con la fórmula y arrastra su controlador de relleno automático hacia abajo para obtener los números duplicados entre las celdas en las demás filas.

=COMPARE(A2,B2,TRUE)

Devolver valores únicos

Selecciona una celda para mostrar los números únicos; en este ejemplo, selecciono la celda E2, luego ingresa la siguiente fórmula y presiona la tecla Enter para obtener los números únicos entre las celdas A2 y B2.

Selecciona la celda con la fórmula y arrastra su controlador de relleno automático hacia abajo para obtener los números únicos entre las celdas en las demás filas.

=COMPARE(A2,B2,FALSE)