Come confrontare i valori separati da virgole in due celle e restituire valori duplicati o univoci in Excel?

Come mostrato nello screenshot sottostante, ci sono due colonne - Colonna1 e Colonna2, ogni cella della colonna contiene numeri separati da virgole. Per confrontare i numeri separati da virgole nella Colonna1 con il contenuto delle celle della stessa riga della Colonna2 e restituire tutti i valori duplicati o univoci, cosa potresti fare?

Questa guida fornisce due metodi per aiutarti a completare questa operazione.

Confronta i valori separati da virgole in due celle e restituisce valori duplicati o univoci con le formule

Questa sezione fornisce due formule per aiutarti a confrontare i valori separati da virgole in due celle e restituire i valori duplicati o univoci tra di loro.

Nota: Le seguenti formule funzionano solo in Excel per 365. Se stai utilizzando altre versioni di Excel, prova a usare il metodo VBA qui sotto.

Prendendo le due colonne sopra come esempio, per confrontare i numeri separati da virgole nella Colonna1 con i numeri separati da virgole nella stessa riga della Colonna2 e restituire valori duplicati o univoci, procedi nel seguente modo.

Restituisci valori duplicati

1. Seleziona una cella per visualizzare i numeri duplicati tra le due celle specificate con numeri separati da virgole; in questo caso, seleziono la cella D2, quindi inserisci la formula sottostante e premi il tasto Invio. Seleziona la cella della formula e trascina la maniglia di riempimento automatico verso il basso per ottenere i numeri duplicati tra le celle nelle altre righe.

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

Restituisci valori univoci

Per restituire i numeri univoci tra le due celle specificate con numeri separati da virgole nella stessa riga, la seguente formula può essere d'aiuto.

1. Seleziona una cella per visualizzare i numeri univoci; in questo caso, seleziono la cella E2, quindi inserisci la formula sottostante e premi il tasto Invio. Seleziona la cella della formula e trascina la maniglia di riempimento automatico verso il basso per ottenere i numeri univoci tra le celle nelle altre righe.

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

Note:

Sblocca la Magia di Excel con Kutools AI

• Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
• Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
• Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
• Interpretazione delle Formule: Comprendi facilmente formule complesse.
• Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.

Potenzia le tue capacità di Excel con strumenti alimentati dall'IA. Scarica Ora ed esperisci un'efficienza mai vista prima!

Confronta due colonne con valori separati da virgole e restituisce valori duplicati o univoci con VBA

La funzione definita dall'utente fornita in questa sezione aiuta a confrontare i valori separati da virgole in due celle specificate e restituire i valori duplicati o univoci tra di loro. Procedi nel seguente modo.

Prendendo lo stesso esempio di sopra, per confrontare i numeri separati da virgole nella Colonna1 con i numeri separati da virgole nella stessa riga della Colonna2 e restituire valori duplicati o univoci, prova la funzione definita dall'utente in questa sezione.

1. Nella cartella di lavoro aperta, premi i tasti Alt + F11 per aprire la finestra Microsoft Visual Basic for Applications.

2. Nella finestra Microsoft Visual Basic for Applications, clicca su Inserisci > Modulo, e copia il seguente codice VBA nella finestra Modulo (Codice).

Codice VBA: Confronta i valori separati da virgole in due celle e restituisce valori duplicati/univoci

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. Dopo aver incollato il codice nella finestra Modulo (Codice), vai su Strumenti > Riferimenti per aprire la finestra Riferimenti – VBAProject, spunta la casella Microsoft Scripting Runtime e poi clicca sul pulsante OK.

4. Premi i tasti Alt + Q per chiudere la finestra Microsoft Visual Basic for Applications.

5. Ora devi applicare due funzioni separatamente per restituire i valori duplicati e univoci da due celle con valori separati da virgole.

Restituisci valore duplicato

Seleziona una cella per visualizzare i numeri duplicati; in questo esempio, seleziono la cella D2, quindi inserisci la formula sottostante e premi il tasto Invio per ottenere i numeri duplicati tra la cella A2 e B2.

Seleziona la cella della formula e trascina la maniglia di riempimento automatico verso il basso per ottenere i numeri duplicati tra le celle nelle altre righe.

=COMPARE(A2,B2,TRUE)

Restituisci valori univoci

Seleziona una cella per visualizzare i numeri univoci; in questo esempio, seleziono la cella E2, quindi inserisci la formula sottostante e premi il tasto Invio per ottenere i numeri univoci tra la cella A2 e B2.

Seleziona la cella della formula e trascina la maniglia di riempimento automatico verso il basso per ottenere i numeri univoci tra le celle nelle altre righe.

=COMPARE(A2,B2,FALSE)