Macro to Compare two columns and identify duplicates

[kr3geyn]

So here I have two columns Column 1 with repetitive values of some 10000 records Column 2 with unique values of 100 records I want to compare Column 1 and Column 2, identify the values in Column 1 that match with Column 2 and highlight them. Conditional formatting for duplicate values doesnt work because Column 1 has repetitive values. Can someone help me with it?

 

[AlphaFrog]

Conditional Format column A
Formula is =COUNTIF(B:B,A1)

 

[JLGWhiz]

Hi kr3geyn, Welcome to MrExcel Forum and Message Board.

I want to compare Column 1 and Column 2, identify the values in Column 1 that match with Column 2 and highlight them.

The statement is vague because them could refer to Column 1 or Column 2 or Both. So I will go with both.

Sub morecolor()
Dim sh As Worksheet, lr As Long, fVal As Range, c As Range
    Set sh = Sheets(1) 'Edit sheet name
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        For Each c In sh.Range("B2:B101") 'Assumes header row
            Set fVal = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not fVal Is Nothing Then
                    fAdr = fVal.Address
                    Do
                    c.Interior.ColorIndex = 6
                    fVal.Interior.ColorIndex = 6
                    fVal.Value = c.Value
                    Set fVal = sh.Range("A2:A" & lr).FindNext(fVal)
                    Loop While fVal.Address <> fAdr
                End If
        Next
End Sub

Then this would be if you only want Column A highlighted.

Sub somecolor()
Dim sh As Worksheet, lr As Long, c As Range
    Set sh = Sheets(1) 'Edit sheet name
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        For Each c In sh.Range("B2:B101") 'Assumes header row
            Set fVal = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not fVal Is Nothing Then                   
                    c.Interior.ColorIndex = 6                    
                End If
        Next
End Sub

And finally, If you only want column B highlighted.

Sub morecolor()
Dim sh As Worksheet, lr As Long, fVal As Range, c As Range
    Set sh = Sheets(1) 'Edit sheet name
    lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
        For Each c In sh.Range("B2:B101") 'Assumes header row
            Set fVal = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not fVal Is Nothing Then
                    fAdr = fVal.Address
                    Do                    
                    fVal.Interior.ColorIndex = 6
                    fVal.Value = c.Value
                    Set fVal = sh.Range("A2:A" & lr).FindNext(fVal)
                    Loop While fVal.Address <> fAdr
                End If
        Next
End Sub

All three codes assume row 1 as header row. If not, simply insert a row at row 1 to compensate.

 

[kr3geyn]

Hi JLGWhiz,

Thank You so much. I want only the column 1 to be highlighted. I have few questions

1. Set sh = Sheets(1) 'Edit sheet name -> In this statement, do you want me to give my sheet name?

2. General question. Is there a column limit for this code? Because when I tried for a dataset with more than 10000 records in Column A, my excel got crashed I am using Excel 07

 

[kr3geyn]

Hi JLGWhiz,

Thank You so much That worked like a magic. It takes little more time for large dataset but the code is perfect

 

[JLGWhiz]

Glad you worked it out.
Regards, JLG

 

[goldenjubilee]

Hi JLGWH,

The above code was really helping. I was able to find duplicate between two columns, but i want macro to highlight duplicate values (Col A and Col B) only when column C (Names) equally matches.

[TABLE="width: 663"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Debit[/TD]
[TD]credit[/TD]
[TD]Names[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD]ramesh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD]Kiran[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Rakesh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]kumar[/TD]
[TD]Here 15 should not highlight though there is a duplicate in other column[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD]kalpesh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]70[/TD]
[TD]ramesh[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD]Kiran[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]raghu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]manoj[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD]sudhakar[/TD]
[TD]****[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]parthu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD]raghu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD]sudhakar[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoyX1]

Thank you, I used the code for one of my projects and worked perfect.
I have another project which is the same concept but I have 15 columns of data (App.Numbers) which I need to run (all 15 columns against each other) for duplicates, meaning, to see if any of the numbers have been duplicated in anyother columns and show me the duplicates on a seperate sheet and/or column 16.

 

[Shriddhi Parab]

Hell All,

I have a complex condition to be applied in my macro. I am new to macros so any guidance will be of help to me.
following is the condition:

please find below sample table:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"]CHECK ID[/TD]
[TD="class: xl64, width: 64"]Remarks[/TD]
[TD="class: xl64, width: 64"]marks[/TD]
[/TR]
[TR]
[TD="class: xl63"]math001[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]math001[/TD]
[TD="class: xl63"]wrong[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]eng002[/TD]
[TD="class: xl63"]wrong[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]math001[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]eng002[/TD]
[TD="class: xl63"]wrong[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]geo003[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]eng002[/TD]
[TD="class: xl63"]wrong[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]geo003[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]geo003[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]geo003[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]sci004[/TD]
[TD="class: xl63"]wrong[/TD]
[TD="class: xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63"]sci004[/TD]
[TD="class: xl63"]correct[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

now for every duplicate entry in column CheckID I want to check the remarks, if first entry is "correct" then marks have to be considered as 1 for that entry, if second occurrence of the CheckiD column is also "correct" then NO marks have to be considered for that entry, i.e marks should be 0 and not 1.

If all the remarks value for any particular CheckID is "wrong", then just one of them should be considered and have marks 0, the rest can be ignored.

 

[Shriddhi Parab]

Conditional Format column A
Formula is =COUNTIF(B:B,A1)

Hello AlphaFrog,

I have given a condition ,where I seek help. Please let me know if you could guide on the same. my post no is #9 on following link :
http://www.mrexcel.com/forum/excel-...-compare-two-columns-identify-duplicates.html