Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting of Duplicate Values in a column | Excel Programming | |||
Highlighting duplicate values in a column | Excel Discussion (Misc queries) | |||
can i not allow duplicate values in the same column? | New Users to Excel | |||
Removing duplicate values in a column | Excel Worksheet Functions | |||
Sum of duplicate values in a column | Excel Programming |