Macro to find duplicate values

**lizzieb** · 01-25-2009, 04:49 PM

I have 2 columns of data. Column 1 has unique values but column 2 has some duplicates of values in column 1. There are about 3000 values in the columns.

I would make active a cell in column 1 then run the macro to find and goto the duplicate value in column 2. Then run again to find if it is duplicated again etc.

Is this possible?

I have attached a sample file.

Thanks
Liz

**Leith Ross** · 01-25-2009, 05:16 PM

Hello lizzieb,

Welcome to the Forum!

Do want to use worksheet formulas to find the duplicates or a VBA macro? What do you want to do with the duplicates once you have found them?

**lizzieb** · 01-25-2009, 06:39 PM

Hi Leith

I am not very knowledgeable on these things so don't really know what I want to use. However I had thought I would use Conditional Formatting to highlight in red the cells that had duplicate values.

Then click on the first cell in column 1 that has duplicates, and run a Macro which would take me to the cell in column 2 with the duplicated value. Then run it again to see if there was a second duplicate of it in column 2 etc. Then if there was no more duplicates of the original value in column 1 the macro would say "no more!"

Then I would start the whole process again with the next duplicated value in column 1.

When the duplicates are found I just want to assess them manually. Not do anything with them.

I tried recording a macro by making the first duplicated cell active then start the macro. Copy the value onto the clipboard, open the find box, paste the contents of clipboard, find next, close, press escape and stop Macro. However this created a macro with just a value in the find box instead of a variable clipboard value.

So I have to give up.

Hope you can help. I am sure it is easy but I can't work it out.

Thanks
Liz

**caholmes** · 01-25-2009, 06:50 PM

If you want to find duplicates using worksheet formulas then use a countif function and autofilter the results.

If you want to find duplicates using code then use this code

Sub FindDuplicates()
Dim RC As Integer
Dim i As Integer, j As Integer

'count the rows in the list
RC = Range("A1").CurrentRegion.Rows.Count

'loop through all the tags to find duplicates
For i = 2 To RC
    For j = 2 To RC
        'uncomment the next line to just show the cell references
        'If Cells(i, 1) = Cells(j, 2) Then Cells(j, 3) = Cells(i, 1).Address(False, False)
        
        'This line shows the cell refernces and creates a hyperlink to it
        If Cells(i, 1) = Cells(j, 2) Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(j, 3), Address:="", SubAddress:= _
                Cells(i, 1).Address(False, False), TextToDisplay:=Cells(i, 1).Address(False, False)
        End If
    Next j
Next i


End Sub

working example for both methods:

**lizzieb** · 01-25-2009, 07:09 PM

Wow - thanks. The Duplicates using VBA is great.

I think that is so much better than I was trying to do.

I shall try it out on the proper data tomorrow and let you know.

Many many thanks

Liz

**lizzieb** · 01-26-2009, 01:48 PM

Dear caholmes
My friend is so pleased with your work. So many thanks. I have now spent the day reading how to use VBA as I would love to understand how it all works. I owe you. Do you have a Paypal account?

Cheers

Liz

**smstha** · 06-04-2010, 03:31 PM

Really thank you so much Mr.

**smstha** · 06-05-2010, 06:18 AM

Hi
Assume my data starting from A:
Then I used to use formula to near column
=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(A:A,A2),"")
Then it will provide result that how many times it is repeated cell.
In this case I need Macro,
Any suggest, please?
Thanks in advance.
Smstha
Chennai

**teylyn** · 06-06-2010, 04:50 AM

smstha,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.