Excel - Macro to highlight Rows with Duplicate values in a cell [Solved/Closed]

ajaydwivediji 14 Posts Tuesday October 30, 2012Registration date January 5, 2017 Last seen - Oct 30, 2012 at 06:26 AM - Latest reply: TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen
- Jan 19, 2015 at 12:06 PM
Geetings,

Kindly help us in creating a macro that highlight rows based on duplicate values in a cell [this cell may contain Alphabets, Numbers or alphanumerics].

For example -

Column A | Column B
Ajay | 001
AJ | 001
VJ | 002
Vijay | 002
SJ | 003
Sanjay | 003

Note: The rows containing the value [001] on Column B should be highlighed in [Light Turquoise color scheme], and the rows containing value [002] on Column B should be highlighted in [Grey-25% color scheme].
Similarly for the rows containing value as [003] should be highlighed again in [Light Turquoise color scheme]

The patern should be same for remaining duplicate entired in entire sheet i.e.
[Light Turquoise color scheme]
[Grey-25% color scheme]
...
..
.

The assistance on this request would be highly appreciated.

Thank you and Best Regards - AD


See more 

11 replies

Best answer
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Aug 22, 2013 at 10:29 AM
4
Thank you
Hi Stalin,

Take a look at the following sample data (column B):
abc
abc
def
ghi
ghi
ghi
ghi
jkl

If you want the result to be 2 (abc and ghi are counted as duplicates) then the following code will do the trick:
Sub test()
Dim x, cDup, lRow As Integer, skipDup As String
lRow = Range("B1").End(xlDown).Row
x = 1
cDup = 0
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value And skipDup <> Cells(x, "B").Value Then
        cDup = cDup + 1
        skipDup = Cells(x, "B").Value
    End If
Loop Until x = lRow + 1
MsgBox "Column B contains " & cDup & " Duplicates."
End Sub

If this isn't what you meant, then please provide some sample data of your own along with the desired result.

Best regards,
Trowa

Thank you, TrowaD 4

Something to say? Add comment

CCM has helped 1867 users this month

TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Oct 30, 2012 at 10:34 AM
0
Thank you
Hi AD,

So basicly if the number in column B is odd then format 1 and if it's even the format 2.

Use the following formula's in conditional format:
=MOD(B1,2)=1 for odd
=MOD(B1,2)=0 for even

Best regards,
Trowa
ajaydwivediji 14 Posts Tuesday October 30, 2012Registration date January 5, 2017 Last seen - Oct 30, 2012 at 11:29 AM
0
Thank you
Dear Trowa,

Thank you so much for attending my request and this is amazing no doubt.

But I have a situation over here, [apologies for not including/mentioning this in my earlier illustrative example] that the number that is being used for de-duping is not actualy appearing in an order.

We have instances where lot of [Odd or Even] numbers are trailing/appearing in a group.

For Example in the following example The name Jyoti/JY is also appearing with an [Odd Id 005] under the [Id 003] on [Column B] -

Column A | Column B
Ajay | 001
AJ | 001
VJ | 002
Vijay | 002
SJ | 003
Sanjay | 003
JY | 005
Jyoti | 005


In this case we this option may lead us to highlighting two different groups with same color despite to the matter of fact they [their IDs] on [Column B] are different.

Kindly advise/suggest.

Thank you and Best Regards - AD
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Oct 30, 2012 at 11:53 AM
Hi AD,

OK that didn't work.

The next thing that I noticed is that each ID is used twice. Is this true for the rest of the data?

Or should I just start by coloring Turquoise and when the ID changes use the color Grey and when it changes again turn to Turquoise again etc...?

Do you want to color the entire row or just column A and B of the respected row?

Best regards,
Trowa

ps. Signing out now, will be back on Thursday.
ajaydwivediji 14 Posts Tuesday October 30, 2012Registration date January 5, 2017 Last seen - Oct 30, 2012 at 01:23 PM
0
Thank you
Dear Trowa,

Thank you once again for responding and understanding the condition that we have in our list. We appreciate it.

Your Question/observation #1 - The occurance of the Id is inconsistence i.e. this Id can appear only once, or more than once, but incase of multiple occrance all of them will appear together. It depends on total number of items grouped into one using this Id.

Your Question/Suggestion # 2 - [just start by coloring Turquoise and when the ID changes use the color Grey and when it changes again turn to Turquoise again etc...] is the best option for such conditions and we want it in this fashion.

Answer to your Question # 3 - We want to color [The Entire Row].

Hope to catch you on Thursday. Please take good care of yourself.

Thank you and Best Regards - Ajay
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Nov 1, 2012 at 10:47 AM
0
Thank you
Hi Ajay,

That is a nice and clear response. The only thing we haven't discussed is if you have a header or not. Since I didn't see one in your example, I assumed you don't have one. If you do, change the forth line "x=1" into "x=2".

Here is the code:
Sub ColorRows()
Dim lRow, x As Integer

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1

Rows(x).Interior.ColorIndex = 34

Loop1:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 34
    Else:
        Rows(x).Interior.ColorIndex = 15
        GoTo Loop2
    End If
Loop Until x = lRow

Loop2:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 15
    Else:
        Rows(x).Interior.ColorIndex = 34
        GoTo Loop1
    End If
Loop Until x = lRow

End Sub

Best regards,
Trowa
ajaydwivediji 14 Posts Tuesday October 30, 2012Registration date January 5, 2017 Last seen - Nov 1, 2012 at 12:20 PM
0
Thank you
~ Simply Wow !!!~ This is awsome. Every row got highlighed the way these were request and should be.

This some thing never seen any where on web.

Kinldy accept our sincere and humble thanks for such a great help!

Note: There is only one bug that shows a message [I think when the processing is over] but results are perfect though

Following is a message displayed on screen.

Microsoft Visual basic
Run-time error '6':
Overflow
[Continue] [End] [Debut] [Help]

When we press/click on [End], then the sheet is returned back. And results are absolutely fine.
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Nov 5, 2012 at 09:42 AM
Thanks for the kind words Ajay!

Concerning the error, I'm thinking you're having more then 32.767 rows.
To solve this change the second line:
"Dim lRow, x As Integer" into Dim "lRow, x As Long"

After some testing I noticed the code would run longer then intended, i.e. a few more rows were colored beyond the last row. To counter this I added two Exit sub lines in the loops. Let me just show you how the revised code looks like:
Sub ColorRows()
Dim lRow, x As Long

lRow = Range("A" & Rows.Count).End(xlUp).Row
x = 1

Rows(x).Interior.ColorIndex = 34

Loop1:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 34
    Else:
        Rows(x).Interior.ColorIndex = 15
        If x = lRow Then Exit Sub
        GoTo Loop2
    End If
Loop Until x = lRow
Exit Sub

Loop2:
Do
    x = x + 1
    If Cells(x, "B").Value = Cells(x - 1, "B").Value Then
        Rows(x).Interior.ColorIndex = 15
    Else:
        Rows(x).Interior.ColorIndex = 34
        If x = lRow Then Exit Sub
        GoTo Loop1
    End If
Loop Until x = lRow

End Sub

Best regards,
Trowa
paul_roshan 2 Posts Sunday January 18, 2015Registration date January 18, 2015 Last seen > TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Jan 18, 2015 at 11:54 AM
The code is seriously great.
My requirement is more different than this can you please help me with that.
Requirement:
ColA ColB ColC ColD
121 Ros Pau RP.G
153 Sud U SU.Y
124 R P RP.C
185 R R RR.G
152 S U SU.C
187 Rit Raj RR.C
127 Ros Pau RP.Y
181 R R RR.Y
155 Sud U SU.G

If the table is as above the requirement is, to highlight the duplicate rows.
That is if ColB and ColC are same value then it is considered as duplicates.

Please help me with this, in case you need any additional input let me know
Great Work. Super. Highlight was simply owesome. Could you help me to count the number of duplicates? this code was fine for me. I need a pop up to display number of duplicates. Could you help me out in this?
TrowaD 2433 Posts Sunday September 12, 2010Registration dateModeratorStatus September 17, 2018 Last seen - Jan 19, 2015 at 12:06 PM
0
Thank you
Hi Paul,

So you want to colour row 2 if B2=C2 and the same for the rows below.

Have you tried Conditional Formatting found under the Start ribbon?

Select row 2, go to conditional formatting, add rule, choose formula and enter: =$B2=$C2, confirm.
Drag row 2 down as far as desired and when the small square appears, choose Apply format only.

Best regards,
Trowa