Cell values based on comparison of previous column cell value.

[Deepk]

hi All,

I want formula or macro that work on the following table and provide the value in column E. The macro should compare the sorted (a-z) value in column D and based on the value from the column C, it provide the below mentioned details in column E.

[TABLE="width: 242"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PAPA[/TD]
[TD]1=2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]TATA[/TD]
[TD]3=4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]FEAR[/TD]
[TD]6=7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]MAMA[/TD]
[TD]8=9[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]GHMF[/TD]
[TD]12=13[/TD]
[/TR]
</tbody>[/TABLE]

Please help me with the code.

Thanks in advance.

 

[Peter_SSs]

Thank you Peter for your help.

You're welcome.

If interested, here is another macro that you could consider.

Sub JoinGroups()
  Dim c As Range
  Dim j As Long, lr As Long
  
  Set c = Range("D2")
  lr = Range("D" & Rows.Count).End(xlUp).Row
  Do Until c.Row > lr
    Do While c.Offset(j + 1).Value = c.Value
      j = j + 1
    Loop
    If j > 0 Then c.Offset(, 1).Resize(j + 1).Value = Join(Application.Transpose(c.Offset(, -1).Resize(j + 1).Value), "=")
    Set c = c.Offset(j + 1)
    j = 0
  Loop
End Sub

 

[MickG]

You're welcome