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"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Car[/TD]
[TD]Bus[/TD]
[TD]Train[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]F1, F2, F3[/TD]
[TD]F1[/TD]
[TD]F1[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]F1, F2, F3[/TD]
[TD]F1, F2, F4[/TD]
[TD]F2, F5, F6[/TD]
[TD]F1, F2[/TD]
[/TR]
[TR]
[TD]F1, R1[/TD]
[TD]R2[/TD]
[TD]F3, F4, R7[/TD]
[TD]No Result[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]R1[/TD]
[TD]No Result[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.