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

#### bb19august

##### New Member
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>

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
ABCD
1CarBusTrainResult
2F1, F2, F3F1F1F1
3F1, F2, F3F1, F2, F4F2, F5, F6F1, F2
4F1, R1R2F3, F4, R7No Result
5R1No Result
6No Result
Sheet2

Code:
``````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
End If
Next i
If Mid(Out, 3) = "" Then
GetRepeaters = "No Result"
Else
GetRepeaters = Mid(Out, 3)
End If
End Function``````

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.

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.

Replies
2
Views
271
Replies
9
Views
396
Replies
5
Views
191
Replies
13
Views
1K
Replies
13
Views
1K

1,203,618
Messages
6,056,317
Members
444,858
Latest member
ucbphd

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back