How to find multiple duplicate values in Excel col

Question

I have a requirement, I have an Excel with values JIRA ID, Defect ID, Duplicate ID, Duplicate

-In JIRA ID column I have all ALM defect ids which has linked JIRA IDs marked.

-I need to take all ALM id which has JIRA ID marked on JIRA ID Column.

-put it against respective JIRA ID on Duplicated ID column separated with (,).

-mark the JIRA id as duplicate

list the duplicate ALM values corresponding JIRA id in Excel using macro

Answer 1

you could try what follows

Option Explicit

Sub main()

Dim dataRng As Range, dbRng As Range, helperRng As Range, cell As Range, found As Range

Set dbRng = Worksheets("DefectConsoliation1").Cells(1, 1).CurrentRegion '<== check if it's your actual sheet name
Set dataRng = dbRng.Offset(1).Resize(dbRng.Rows.Count - 1)
Set helperRng = dbRng.Offset(dbRng.Rows.Count + 1, dbRng.Columns.Count + 1).Cells(1, 1)

dataRng.Columns(3).SpecialCells(xlCellTypeConstants, xlTextValues).Copy Destination:=helperRng

With helperRng.CurrentRegion
    If .Rows.Count > 1 Then .RemoveDuplicates Columns:=Array(1), Header:=xlNo
    For Each cell In .CurrentRegion
        Set found = dataRng.Columns(2).Find(what:=cell.Value, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=True)
        If Not found Is Nothing Then
            dbRng.AutoFilter field:=3, Criteria1:=cell.Value
            found.Offset(, 1).Resize(, 2) = Array(GetString(dataRng.Columns(1).SpecialCells(xlCellTypeVisible)), "duplicate")
            .AutoFilter
        End If
    Next cell
    .ClearContents
End With

End Sub

Function GetString(rng As Range) As String
Dim cell As Range

For Each cell In rng
    GetString = GetString & cell.Value & ","
Next cell
If Len(GetString) > 0 Then GetString = Left(GetString, Len(GetString) - 1)

End Function

it makes use of a "helper" range, occupying one column only and starting from a cell two columns at the right and two rows below your data CurrentRegion (i.e. as per your data example, the "helper" range would be from cell F19 downwards). If you have relevant data in such localized data, you should change code accordingly