Highlighting duplicates in one column based on the data from other column

[Deepk]

Hey guys,

Please see my sample data below. The data could be in any columns.

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]
I want to have a macro that highlight (using interior color) the data in the first column (any column) based on the duplicates data from the other column (any column). The macro should work in following steps.

After selecting the data in first column I run the macro.
I enter the column number for the column having duplicate entries, in an inputbox
The macro generate the following output

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3 red[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4 red[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]6 red[/TD]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]

I hope my requirement is clear. Thank you in advance.

 

[Joe4]

Does it really need to be a Macro? Because this can be done pretty easily with Conditional Formatting.
The advantage to Conditional Formatting is that it requires no code and it is dynamic (it changes automatically with the data changing)

So, if your data was in the range A1:B6, here is how you would do what you want:
1. Select the range A1:A6.
2. Go to Conditional Formatting and select the Formula option
3. Enter the following Conditional Formatting formula:
=countif($B$1:$B1,$B1)>1
4. Choose your desired formatting option
5. Click OK

That's it! You can also set this Conditional Formatting via VBA code by turning on the Macro Recorder and recording yourself performing the steps above. The only part you would need to edit is the code to select all the appropriate cells in column B, but that is pretty easy, if you need help with that.

 

[Deepk]

Does it really need to be a Macro? Because this can be done pretty easily with Conditional Formatting.
The advantage to Conditional Formatting is that it requires no code and it is dynamic (it changes automatically with the data changing)

So, if your data was in the range A1:B6, here is how you would do what you want:
1. Select the range A1:A6.
2. Go to Conditional Formatting and select the Formula option
3. Enter the following Conditional Formatting formula:
=countif($B$1:$B1,$B1)>1
4. Choose your desired formatting option
5. Click OK

That's it! You can also set this Conditional Formatting via VBA code by turning on the Macro Recorder and recording yourself performing the steps above. The only part you would need to edit is the code to select all the appropriate cells in column B, but that is pretty easy, if you need help with that.

hi Joe4,

Thank you for your suggestion. it works, however, every time we have to work on different files and applying formula in conditional formatting, removing duplicate data, clearing the conditional sending the files to other is time taking. So if you provide me the macro it will be good. we can add this in add-in. Here macro recording is not working as the column numbers are not fixed. thank you.

 

[Joe4]

Here macro recording is not working as the column numbers are not fixed. thank you.

Do you really mean column numbers or do you mean row numbers?

If the column numbers aren't fixed, how is the VBA code we write going to know which columns to do this to?

 

[Deepk]

Do you really mean column numbers or do you mean row numbers?

If the column numbers aren't fixed, how is the VBA code we write going to know which columns to do this to?

It is fixed for one excel worksheet/workbook but for another one the column having duplicates may be different. In macro we can enter the column number in an inputbox. And this macro can be applied to any worksheet/workbook. I hope it is clear.

 

[Joe4]

So, in these situation, it looks like there are two columns at play. The right column is where you are looking for duplicates, and the left column is the one that the highlighting will be applied to. So, do we want to prompt them for the left or the right column?

And, is our duplicate search always going to begin with row 1? If not, how will we determine which row to start on?

 

[Deepk]

So, in these situation, it looks like there are two columns at play. The right column is where you are looking for duplicates, and the left column is the one that the highlighting will be applied to. So, do we want to prompt them for the left or the right column?

And, is our duplicate search always going to begin with row 1? If not, how will we determine which row to start on?

Yes, you are right.

The data in left column will be selected manually. Based on the selection it starts identifying duplicates from corresponding cells of right column (column number will be provided in an inputbox) and highlight cells in the left column. Hope it is clear. Thank you.

 

[Joe4]

OK, this code should work for any columns out to column Z. If you may go out further than that, we would need to make some changes to it:

Sub MyFormatMacro()

    Dim col As String
    Dim lrow As Long
    Dim col2 As String
    Dim rng As Range
    Dim fRng As String
    
'   Prompt to enter column letter
    col = InputBox("Please enter column letter you wish to apply this formatting to")
    
'   Find last row in column
    lrow = Cells(Rows.Count, col).End(xlUp).Row
    
'   Get letter of second column
    col2 = Chr(Cells(1, col).Offset(0, 1).Column + 64)

'   Set range to apply conditional formatting to
    Set rng = Range(Cells(1, col), Cells(lrow, col))
    
'   Build formula range
    fRng = "$" & col2 & "$1:$" & col2 & "1,$" & col2 & "1"
    MsgBox fRng
    
'   Apply conditional formatting
    With rng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & fRng & ")>1"
        .FormatConditions(rng.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
        
End Sub

 

[Deepk]

OK, this code should work for any columns out to column Z. If you may go out further than that, we would need to make some changes to it:

Sub MyFormatMacro()

    Dim col As String
    Dim lrow As Long
    Dim col2 As String
    Dim rng As Range
    Dim fRng As String
    
'   Prompt to enter column letter
    col = InputBox("Please enter column letter you wish to apply this formatting to")
    
'   Find last row in column
    lrow = Cells(Rows.Count, col).End(xlUp).Row
    
'   Get letter of second column
    col2 = Chr(Cells(1, col).Offset(0, 1).Column + 64)

'   Set range to apply conditional formatting to
    Set rng = Range(Cells(1, col), Cells(lrow, col))
    
'   Build formula range
    fRng = "$" & col2 & "$1:$" & col2 & "1,$" & col2 & "1"
    MsgBox fRng
    
'   Apply conditional formatting
    With rng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF(" & fRng & ")>1"
        .FormatConditions(rng.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
    End With
        
End Sub

Hi Joe4,

Thank you for the code. But unfortunately it is not working for me. I execute the code but nothing happened. Please note the following:

The data in the first column is already in selection (before running the code).
In the inputbox I have to enter the column letter for the second column (till Z is fine)
I dont want any popup or msgbox to appear during the execution (here it is MsgBox fRng)
Duplicates are in second column only
The macro should highlighted the cells in first column.
If you are using conditional formatting through macro, the conditional formatting should be removed at the end of execution without removing the cell colors

Please find below a sample data.

[TABLE="width: 177"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column C
Colored ?[/TD]
[TD]Column K
Forinputbox[/TD]
[/TR]
[TR]
[TD]1 No[/TD]
[TD]tata[/TD]
[/TR]
[TR]
[TD]2 No[/TD]
[TD]mata[/TD]
[/TR]
[TR]
[TD]3 No[/TD]
[TD]bata[/TD]
[/TR]
[TR]
[TD]4 No[/TD]
[TD]fafa[/TD]
[/TR]
[TR]
[TD]5 Yes[/TD]
[TD]mata[/TD]
[/TR]
[TR]
[TD]6 Yes[/TD]
[TD]tata[/TD]
[/TR]
[TR]
[TD]7 No[/TD]
[TD]mama[/TD]
[/TR]
[TR]
[TD]8 Yes[/TD]
[TD]fafa[/TD]
[/TR]
[TR]
[TD]9 Yes[/TD]
[TD]bata[/TD]
[/TR]
[TR]
[TD]10 No[/TD]
[TD]fhbv[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

See comments below:

The data in the first column is already in selection (before running the code).
In the inputbox I have to enter the column letter for the second column (till Z is fine)

I misunderstood. I thought that you wanted the input box to request the first column, not the second. If you enter the first column, it will probably work.
Are the two columns that you want involved always side-by-side? If so, and you always have the first column already selected, then there shouldn't be any need for an Input Box.

I dont want any popup or msgbox to appear during the execution (here it is MsgBox fRng)

Sorry, I just put that in there to test. It can be removed.

If you are using conditional formatting through macro, the conditional formatting should be removed at the end of execution without removing the cell colors

Well, that is not quite how Conditional Formatting works. You cannot tell it to remove the Conditional Formatting rules, but leave the formatting.
Why do we want/need to remove the Conditional Formatting rules?
If we truly need to do so, then we will need to take a different approach, as this one won't work.