Assigning name for Unique and Duplicated Values in a Column

[madhuchelliah]

Hello all, i have data in A column, which has unique values and duplicated values. I want to assign a name like Group 1, Group 2....
VBA has to look into all the data in A column and assign group name in D column. If any value is repeated it should be assigned with same Group name.

Thank you.
Example

Book1
	A	B	C	D
1	Name	XXX	YYY	Group
2	AAA			Group - 1
3	AAA			Group - 1
4	CCC			Group - 2
5	DDD			Group - 3
6	EEE			Group - 4
7	EEE			Group - 4
8	EEE			Group - 4
9	FFF			Group - 5
10	FFF			Group - 5
11	GGG			Group - 6
12	HHH			Group - 7
Sheet1

 

[bebo021999]

With column A is sorted
In D2:

="Group - " & SUMPRODUCT(1/COUNTIF($A$2:A2,$A$2:A2))

 

[madhuchelliah]

With column A is sorted
In D2:

="Group - " & SUMPRODUCT(1/COUNTIF($A$2:A2,$A$2:A2))

Hey, Thanks for your reply. i am looking for VBA solution. Could you please help on that.
Thank you.

 

[Peter_SSs]

Try this with a copy of your worksheet.

Sub GroupNum()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp))
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1)) Then d(a(i, 1)) = "Group - " & d.Count + 1
    a(i, 1) = d(a(i, 1))
  Next i
  Range("D2").Resize(UBound(a)).Value = a
End Sub

 

[madhuchelliah]

Try this with a copy of your worksheet.

Sub GroupNum()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp))
  For i = 1 To UBound(a)
    If Not d.exists(a(i, 1)) Then d(a(i, 1)) = "Group - " & d.Count + 1
    a(i, 1) = d(a(i, 1))
  Next i
  Range("D2").Resize(UBound(a)).Value = a
End Sub

Hello Pete, it is working as expected. Thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.