Formula to compare various cells in a row and give result if it is common in multiple cells

[bb19august]

Dear All,
Need help with a excel formula to compare various cells in a row and give result if it is common data in multiple cells

Condition:
- if the cells have a common alphanumeric in a row, at least two or above, then print it as result else "No Result"

- different alphanumeric can be separated by commas also in the cells

- If there are two alphanumeric, common in the cells then the result to be separated by comma

- if there are no data (blank) in the cells then result is "No result"

Car	Bus	Train	Result
F1, F2, F3	F1	F1	F1
F1, F2, F3	F1, F2, F4	F2, F5, F6	F1, F2
F1, R1	R2	F3, F4, R7	No Result
		R1	No Result

<tbody>
</tbody>

 

[JoeMo]

Re: Need help with a excel formula to compare various cells in a row and give result if it is common in multiple cells

Here's a UDF you can try. Use it like a native Excel worksheet function as in the example below.

Excel Workbook
	A	B	C	D
1	Car	Bus	Train	Result
2	F1, F2, F3	F1	F1	F1
3	F1, F2, F3	F1, F2, F4	F2, F5, F6	F1, F2
4	F1, R1	R2	F3, F4, R7	No Result
5			R1	No Result
6				No Result
Sheet2

Function GetRepeaters(R As Range) As String
Dim d As Object, x As Variant, i As Long, S As String, Out As String
Set d = CreateObject("Scripting.Dictionary")
S = Join(Application.Index(R.Value, 1, 0), ", ")
x = Split(S, ", ")
For i = LBound(x) To UBound(x)
    If d.exists(x(i)) And InStr(Out, x(i)) = 0 Then
        Out = Out & ", " & x(i)
    ElseIf Not d.exists(x(i)) Then
        d.Add x(i), d.Count
    End If
Next i
If Mid(Out, 3) = "" Then
    GetRepeaters = "No Result"
Else
    GetRepeaters = Mid(Out, 3)
End If
End Function

 

[bb19august]

Re: Need help with a excel formula to compare various cells in a row and give result if it is common in multiple cells

You are still young Result as expected. Thank you so much for your time and help.

 

[JoeMo]

Re: Need help with a excel formula to compare various cells in a row and give result if it is common in multiple cells

You are still young Result as expected. Thank you so much for your time and help.

You are welcome - thanks for the reply.