Match two or more rows values based on their ID in another cell

[aayaanmayank]

---

Hi, Can any one help me in this, i have a column contains ID and another column contains company names.

i have to first check that how many cells or times one/same id contains and then match and color code (if matches then green else yellow) company name with the similar times.
Below is the data-
[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD]AE36630GU[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]PHARMACEUTICAL INDUSTRIES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]AE36630GU[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]PHARMACEUTICAL INDUSTRIES[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST SH.P.K (L.T.D.)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALJ61924662T[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]ABC INVEST SHPK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK43128401L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]INVEST PETROLEUM ALBANIA LTD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK43128401L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]INVEST PETROLEUM ALBANIA LTD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH HYDROPOWER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH HYDROPOWER SH.A.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl65"]ALK82418662C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65"]RAH Hydropower Sh.A.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 293"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
so far i have prepared a macro which is moving row by row but that only matches company names with previous row

Set SHGROUP = ThisWorkbook.Worksheets("Data_DQ")


Range("F2").Select
ActiveCell.Interior.Color = vbYellow


For U = 2 To LastRow1


Set MYNAME2 = Cells(U, "f")
Set MYNAME3 = Cells((U + 1), ("f"))


If MYNAME2.Value = MYNAME3 Then


SHGROUP.Cells(U, "f").Interior.Color = vbGreen
SHGROUP.Cells(U + 1, "f").Interior.Color = vbGreen
Else


SHGROUP.Cells(U + 1, "f").Interior.Color = vbYellow


End If
Next U

 

[aayaanmayank]

How about

Sub HiliteCells()
   Dim cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(cl.Value) Then
         Dic.Add cl.Value, CreateObject("scripting.dictionary")
         Dic(cl.Value).Add cl.Offset(, 1).Value, cl.Offset(, 1)
         cl.Offset(, 1).Interior.Color = vbYellow
      ElseIf Not Dic(cl.Value).exists(cl.Offset(, 1).Value) Then
         Dic(cl.Value).Add cl.Offset(, 1).Value, cl.Offset(, 1)
         cl.Offset(, 1).Interior.Color = vbYellow
      Else
         cl.Offset(, 1).Interior.Color = vbGreen
         Dic(cl.Value)(cl.Offset(, 1).Value).Interior.Color = vbGreen
      End If
   Next cl
End Sub

[/i have tried this code it is not working properly Below is my data. it is throwing "Yellow Color" in 1st and 2nd row, rest are Green ][TABLE="width: 292"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICES[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESINTERNATIONALL[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
</tbody>[/TABLE]

 

[aayaanmayank]

How about

Sub HiliteCells()
   Dim cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   For Each cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(cl.Value) Then
         Dic.Add cl.Value, CreateObject("scripting.dictionary")
         Dic(cl.Value).Add cl.Offset(, 1).Value, cl.Offset(, 1)
         cl.Offset(, 1).Interior.Color = vbYellow
      ElseIf Not Dic(cl.Value).exists(cl.Offset(, 1).Value) Then
         Dic(cl.Value).Add cl.Offset(, 1).Value, cl.Offset(, 1)
         cl.Offset(, 1).Interior.Color = vbYellow
      Else
         cl.Offset(, 1).Interior.Color = vbGreen
         Dic(cl.Value)(cl.Offset(, 1).Value).Interior.Color = vbGreen
      End If
   Next cl
End Sub

[/i have tried this code it is not working properly Below is my data. it is throwing "Yellow Color" in 1st and 2nd row, rest are Green ][TABLE="width: 292"]
<tbody>[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICES[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESINTERNATIONALL[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
[TR]
[TD="align: right"]409396591[/TD]
[TD]ABCENERGYSERVICESLIMITEDLIABILITYCOMPANY[/TD]
[/TR]
</tbody>[/TABLE]
[However it should through Yellow color in all the cells as first value is not match with rest]