Como comparar valores separados por vírgula em duas células e retornar valores duplicados ou únicos no Excel?
Conforme mostrado na captura de tela abaixo, há duas colunas - Column1 e Column2, cada célula na coluna contém números separados por vírgulas. Para comparar os números separados por vírgula na Coluna1 com o conteúdo da célula na mesma linha da Coluna2 e retornar todos os valores duplicados ou exclusivos, o que você poderia fazer?
Este tutorial fornece dois métodos para ajudá-lo a realizar essa tarefa.
Compare valores separados por vírgula em duas células e retorne valores duplicados ou exclusivos com fórmulas
Esta seção fornece duas fórmulas para ajudar a comparar os valores separados por vírgulas em duas células e retornar os valores duplicados ou exclusivos entre elas.
Note: As fórmulas a seguir funcionam apenas em Excel para 365. Se você estiver usando outras versões do Excel, tente usar o método VBA abaixo.
Tome as duas colunas acima como exemplo, para comparar os números separados por vírgula na Coluna1 com os números separados por vírgula na mesma linha da Coluna2 e retornar valores duplicados ou exclusivos, faça o seguinte.
Retornar valores duplicados
1. Selecione uma célula para gerar os números duplicados entre as duas células especificadas com números separados por vírgula, neste caso, selecione a célula D2, insira a fórmula abaixo e pressione o botão Entrar chave. Selecione a célula da fórmula e arraste sua Identificador de preenchimento automático para baixo para obter os números duplicados entre as células nas outras linhas.
=LET(x, TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),y,UNIQUE(x),z,UNIQUE(x,,1), TEXTJOIN(", ",TRUE,IF(ISERROR(MATCH(y,z,0)),y, "")))
Retornar valores únicos
Para retornar os números exclusivos entre as duas células especificadas com números separados por vírgula na mesma linha, a seguinte fórmula pode ajudar.
1. Selecione uma célula para gerar os números exclusivos, neste caso, selecione a célula E2, digite a fórmula abaixo e pressione o botão Entrar chave. Selecione a célula da fórmula e arraste sua Identificador de preenchimento automático para baixo para obter os números únicos entre as células nas outras linhas.
=TEXTJOIN(", ",TRUE,UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ",TRUE,A2:B2), ", ")),,1))
Notas:
Compare duas colunas com valores separados por vírgula e retorne valores duplicados ou exclusivos com VBA
A função definida pelo usuário fornecida nesta seção ajuda a comparar os valores separados por vírgula em duas células especificadas e retornar os valores duplicados ou valores exclusivos entre eles. Por favor, faça o seguinte.
Pegue o mesmo exemplo acima, para comparar os números separados por vírgula na Coluna1 com os números separados por vírgula na mesma linha da Coluna2 e retornar valores duplicados ou exclusivos, tente a função definida pelo usuário nesta seção.
1. Na pasta de trabalho de abertura, pressione o botão outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. No Microsoft Visual Basic para Aplicações janela, clique em inserção > Móduloe copie o seguinte código VBA no Módulo (Código) janela.
Código VBA: Compare valores separados por vírgulas em duas células e retorne valores duplicados/exclusivos
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. Depois de colar o código no Módulo (Código) janela, clique em Ferramentas > Referências para abrir o Referências - VBAProject janela, verifique o Tempo de execução de scripts da Microsoft caixa e, em seguida, clique no botão OK botão.
4. aperte o outro + Q chaves para fechar o Microsoft Visual Basic para Aplicações janela.
5. Agora você precisa aplicar duas funções separadamente para retornar os valores duplicados e exclusivos de duas células de valor separadas por vírgula.
Retornar valor duplicado
Selecione uma célula para gerar os números duplicados, neste exemplo, selecionei a célula D2, digite a fórmula abaixo e pressione a tecla Entrar para obter os números duplicados entre as células A2 e B2.
Selecione a célula da fórmula e arraste sua alça de preenchimento automático para baixo para obter os números duplicados entre as células nas outras linhas.
=COMPARE(A2,B2,TRUE)
Retornar valores únicos
Selecione uma célula para gerar os números exclusivos, neste exemplo, selecionei a célula E2, digite a fórmula abaixo e pressione a tecla Entrar para obter os números exclusivos entre as células A2 e B2.
Selecione a célula da fórmula e arraste sua alça de preenchimento automático para baixo para obter os números exclusivos entre as células nas outras linhas.
=COMPARE(A2,B2,FALSE)
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!