Duplicate values in column A and different in column B
 

Hi All,
Please can anyway know how to get the result as below.

Original data :

Col_A Col_B
sachin 1
saurav 2
rahul 3
viru 4
yuvaraj 5
anil 6
srinath 7
sachin 8
saurav 9
rahul 10
anil 11
sachin 12
rahul 13
viru 14


The result shud be like this in Column C:
For all the duplicate entries in column A the valuse in column B are
unique. So all the values in column B shud be printed in column C.
So then we can use another code of removing duplicate lines and removes
all duplicate enries in column A .
This has added advantede as we will not loose valuse in column B. As
happens in usual remove duplicate lines code that I am finding in this
blog.

Col_A Col_B Col_C
sachin 1 1, 8, 12
saurav 2 2, 9
rahul 3 3, 10, 13
viru 4 4, 14
yuvaraj 5 5
anil 6 6, 11
srinath 7 7
sachin 8
saurav 9
rahul 10
anil 11
sachin 12
rahul 13
viru 14

Thanks.

Duplicate values in column A and different in column B
 

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iStart As Long
Dim sTmp As String
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C1").Value = Range("B1").Value
For i = 2 To iLastRow + 1
On Error Resume Next
iStart = Application.Match(Cells(i, "A").Value,
Range("A1").Resize(i - 1), 0)
On Error GoTo 0
If iStart 0 Then
Cells(iStart, "C").Value = Cells(iStart, "C").Value & "," &
Cells(i, "B").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
Cells(i, "C").Value = Cells(i, "B").Value
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi All,
Please can anyway know how to get the result as below.

Original data :

Col_A Col_B
sachin 1
saurav 2
rahul 3
viru 4
yuvaraj 5
anil 6
srinath 7
sachin 8
saurav 9
rahul 10
anil 11
sachin 12
rahul 13
viru 14


The result shud be like this in Column C:
For all the duplicate entries in column A the valuse in column B are
unique. So all the values in column B shud be printed in column C.
So then we can use another code of removing duplicate lines and removes
all duplicate enries in column A .
This has added advantede as we will not loose valuse in column B. As
happens in usual remove duplicate lines code that I am finding in this
blog.

Col_A Col_B Col_C
sachin 1 1, 8, 12
saurav 2 2, 9
rahul 3 3, 10, 13
viru 4 4, 14
yuvaraj 5 5
anil 6 6, 11
srinath 7 7
sachin 8
saurav 9
rahul 10
anil 11
sachin 12
rahul 13
viru 14

Thanks.