vba code find duplicates

[asyamonique]

Dim Rng As Range, Dub As Integer, Dn As Range, col As IntegerDim Dn2 As Range, c As Integer
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))


For Dub = 0 To 1
    Set Rng = Rng.Offset(, Dub)
        col = Dub + 8


    For Each Dn In Rng
        For Each Dn2 In Rng.Offset(, 2)
            If WorksheetFunction.CountIf(Rng.Offset(, 4), Dn) = 0 Then
                If Dn = Dn2 Then
                    c = c + 1
                    Cells(c, col) = Dn
                 End If
            End If
        Next Dn2
    Next Dn
c = 0
Next Dub

Good Day,
The code given above populating duplicate entries between 2 column "A-B &C-D"
Is it possible to alter that code which will get the datas in first 2 columns?
Thanks

 

[tusharm]

I'm sure the code and the explanation makes sense to you but I'm lost. Can you explain -- without a code dump -- on what you want to do?

Dim Rng As Range, Dub As Integer, Dn As Range, col As IntegerDim Dn2 As Range, c As Integer
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))


For Dub = 0 To 1
    Set Rng = Rng.Offset(, Dub)
        col = Dub + 8


    For Each Dn In Rng
        For Each Dn2 In Rng.Offset(, 2)
            If WorksheetFunction.CountIf(Rng.Offset(, 4), Dn) = 0 Then
                If Dn = Dn2 Then
                    c = c + 1
                    Cells(c, col) = Dn
                 End If
            End If
        Next Dn2
    Next Dn
c = 0
Next Dub

Good Day,
The code given above populating duplicate entries between 2 column "A-B &C-D"
Is it possible to alter that code which will get the datas in first 2 columns?
Thanks

 

[asyamonique]

Hi,
Please chek below sample,
Just trying to get the duplicates from two columns.
Sorry for the late reply by the way!

COLUMN A	COLUMN B		COLUMN D	COLUMN E
APPLE	1234		APPLE	1234
ORANGE	7654		ORANGE	7654
MELON	3456		MELON	3456
APPLE	1234		LEMON	4378
ORANGE	7654
MELON	3456
APPLE	1234
ORANGE	7654
MELON	3456
APPLE	1234
ORANGE	7654
MELON	3456
APPLE	1234
LEMON	4378

<colgroup><col span="5"></colgroup><tbody>
</tbody>

 

[JLGWhiz]

It is still hazy to me. Do you just want to find the duplicates in columns A and B, where the values in both columns are repeated in other rows, or do you want to match them to columns D & E? Or do you want the found duplicates in A and B listed in D and E as Unique Values? Or what exactly do you want done with the duplicates once found?

 

[Trebor76]

ADVERTISEMENT

Hi asyamonique,

Based on the assumption that you're only checking for uniques in Col. A try this:

Option Explicit
Sub Macro1()

    Dim rngCell     As Range
    Dim lngMyRow    As Long
    Dim wsSourceTab As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsSourceTab = Sheets("Sheet1") 'Sheet name with data. Change to suit.

    With CreateObject("Scripting.Dictionary")
        For Each rngCell In wsSourceTab.Range("A2:A" & wsSourceTab.Range("A" & Rows.Count).End(xlUp).Row) 'Works down from cell A2. Change to suit.
            If Len(rngCell.Value) > 0 Then
                If Not .Exists(rngCell.Value) Then
                    .Add rngCell.Value, rngCell.Value
                    If lngMyRow = 0 Then
                        lngMyRow = 2 'Initial output row is 2. Change to suit.
                    Else
                        lngMyRow = lngMyRow + 1
                    End If
                    wsSourceTab.Range("A" & rngCell.Row & ":B" & rngCell.Row).Copy Destination:=wsSourceTab.Range("C" & lngMyRow)
                End If
            End If
        Next rngCell
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[asyamonique]

Many Thanks Trebor,

 

[asyamonique]

ADVERTISEMENT

Trebor,
If you don't mind could you help me for the sample below as well?
Thats for single column..
Thanks

COLUMN A	COLUMN B
100007211	100007211
100007291	100007291
100001196	100001196
100007214	100007214
100007235	100007235
100007205	100007205
100007211
100007291
100001196
100007214
100007235
100007205
100007211
100007291
100001196
100007214
100007235
100007205
100007211
100007291
100001196
100007214
100007235
100007205
100007211
100007291
100001196
100007214
100007235
100007205
100007211
100007291
100001196
100007214
100007235
100007205
100007211
100007291
100001196

<colgroup><col span="2"></colgroup><tbody>
</tbody>

 

[Joe4]

You re-posted your new question here: http://www.mrexcel.com/forum/excel-questions/982608-formula-get-duplicate-entries.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #12 here: Forum Rules).

 

[asyamonique]

Hi Joe4,
First one was base on vba code request,
Second was a formula request,
Sorry for it anyway
Cheers

 

[Trebor76]

Note sure why you want a VBA and formula solution as you can only use one or the other but for my macro you just need to change this line of code...

wsSourceTab.Range("A" & rngCell.Row & ":B" & rngCell.Row).Copy Destination:=wsSourceTab.Range("C" & lngMyRow)

...to this:

wsSourceTab.Range("A" & rngCell.Row).Copy Destination:=wsSourceTab.Range("B" & lngMyRow)

Regards,

Robert