Para gerar "os valores correspondentes correspondentes separados por vírgulas", gostaria de sugerir o código VBA em vez de qualquer fórmula complexa, que é mais rápida e melhor.
- Pressione ALT + F11 para abrir as janelas do Editor VB.
- Selecione a Planilha na janela Explorador de Projetos.
- Clique com o botão direito e selecione Inserir, Módulo.
- Copiar & Cole os dois.
Como usar: =GetDiffs1(A2,B2)&", "& GetDiffs2(A2,B2)
Function GetDiffs1(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long
Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
For lLoop = 0 To UBound(Array1)
strDiff = vbNullString
strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0))
If strDiff = vbNullString Then
lCheck = 0
lCheck = .Match(Array1(lLoop), Array2, 0)
If lCheck = 0 Then
strDiffs = strDiffs & "," & Array1(lLoop)
End If
End If
Next lLoop
End With
GetDiffs1 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function
Function GetDiffs2(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long
Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
For lLoop = 0 To UBound(Array2)
strDiff = vbNullString
strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0))
If strDiff = vbNullString Then
lCheck = 0
lCheck = .Match(Array2(lLoop), Array1, 0)
If lCheck = 0 Then
strDiffs = strDiffs & "," & Array2(lLoop)
End If
End If
Next lLoop
End With
GetDiffs2 = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function