Find Common Text in 3 Rows

[lafata2000]

Hi all... I need to be able to get the most common text in a string out of 3 rows..... Example below:

Column 1	Column 2	Column 3		Formula Needed
ARM KIT,,FR SUSP RH	CONTROL ARM	Suspension Control Arm		Arm
Sensor	OIL LEVEL SENDER	Engine Oil Level Sensor		Sensor
TS WATER PUMP	Water Pump 1 EA CQWPS	Engine Water Pump		Water Pump

I have tried the below and it is does not return any text..... any thoughts?
=INDEX(B2:D2,MODE(MATCH(B2:D2,B2:D2,0)))
=INDEX(B2:D2,MODE(IF(ISTEXT(B2:D2),MATCH(B2:D2,B2:D2,0))))

 

[JEC]

How about this.
Make sure your data has a header. The loop starts at row 2.

Sub jec()

 Dim dict As Object
 Dim jv, ar, sp, it As Variant
 Dim i As Long
 Dim c00 As String

 Set dict = CreateObject("scripting.dictionary")
 dict.CompareMode = 1
 jv = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 5)
 
 For i = 2 To UBound(jv)
    ar = Split(Replace(Join(Array(jv(i, 1), jv(i, 2), jv(i, 3))), ",,", " "))
    With Cells(1, 100).Resize(UBound(ar) + 1)
       .ClearContents
       .Value = Application.Transpose(ar)
        sp = Filter(Application.Transpose(Evaluate(Replace("if(max(countif(@@,@@))=countif(@@,@@),@@,""~"")", "@@", .Address))), "~", False)
    End With
  
    For Each it In sp
      c00 = dict(it)
    Next
 
    jv(i, 5) = Join(dict.keys, ", ")
    dict.RemoveAll
 Next

 Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 5) = jv
End Sub

 

[lafata2000]

How about this.
Make sure your data has a header. The loop starts at row 2.

Sub jec()

 Dim dict As Object
 Dim jv, ar, sp, it As Variant
 Dim i As Long
 Dim c00 As String

 Set dict = CreateObject("scripting.dictionary")
 dict.CompareMode = 1
 jv = Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 5)
 
 For i = 2 To UBound(jv)
    ar = Split(Replace(Join(Array(jv(i, 1), jv(i, 2), jv(i, 3))), ",,", " "))
    With Cells(1, 100).Resize(UBound(ar) + 1)
       .ClearContents
       .Value = Application.Transpose(ar)
        sp = Filter(Application.Transpose(Evaluate(Replace("if(max(countif(@@,@@))=countif(@@,@@),@@,""~"")", "@@", .Address))), "~", False)
    End With
 
    For Each it In sp
      c00 = dict(it)
    Next
 
    jv(i, 5) = Join(dict.keys, ", ")
    dict.RemoveAll
 Next

 Sheets(1).Cells(1, 1).CurrentRegion.Resize(, 5) = jv
End Sub

You are so awesome! Thank you so much! Worked like a charm!

 

[JEC]

You're welcome!

 

[lafata2000]

You're welcome!

Hi JEC, question for you....... it seems as though the VBA code is not comparing Column 1 with 2 and 3, is it possible to do all 3?

Column 1	Column 2	Column 3	Formula
LATCH	LOCK ACTUATOR	LIFTGATE ACTUATOR	ACTUATOR
LATCH	Lock Actuator 1 PC DORMA	LIFTGATE ACTUATOR	Actuator
LOCK	INTGR LATCH ACTUATOR	LIFTGATE ACTUATOR	ACTUATOR
LOCK	INTGR LATCH ACTUATOR 1 PC DORM	LIFTGATE ACTUATOR	ACTUATOR