Highlight Duplicates from Two Columns with Different Colours

G

gacicd

New Member
Joined
Nov 30, 2012
Messages
30

I have two columns A1 (Section X) and B1 (Section Y) <o:p></o:p>

Each column has 20 rows of data (A2-B21) such as: <o:p></o:p>

Section X​

Section Y​

1<o:p></o:p>​

33<o:p></o:p>​

2<o:p></o:p>​

34<o:p></o:p>​

3<o:p></o:p>​

35<o:p></o:p>​

4<o:p></o:p>​

36<o:p></o:p>​

5<o:p></o:p>​

37<o:p></o:p>​

3<o:p></o:p>​

35<o:p></o:p>​

7<o:p></o:p>​

39<o:p></o:p>​

8<o:p></o:p>​

40<o:p></o:p>​

9<o:p></o:p>​

41<o:p></o:p>​

10<o:p></o:p>​

42<o:p></o:p>​

11<o:p></o:p>​

43<o:p></o:p>​

12<o:p></o:p>​

44<o:p></o:p>​

10<o:p></o:p>​

42<o:p></o:p>​

14<o:p></o:p>​

46<o:p></o:p>​

10<o:p></o:p>​

42<o:p></o:p>​

16<o:p></o:p>​

48<o:p></o:p>​

17<o:p></o:p>​

49<o:p></o:p>​

18<o:p></o:p>​

50<o:p></o:p>​

19<o:p></o:p>​

51<o:p></o:p>​

20<o:p></o:p>​

52<o:p></o:p>​

<tbody>
</tbody>
<o:p> </o:p>
What I need is either dynamic formula or macros that can do thefollowing:

1) Assess range A2-B21; <o:p></o:p>
<o:p> </o:p>
2) Identify sets of duplicates based on the identicalvalues in both columns - for example in this case there are two sets of duplicates:<o:p></o:p>
<o:p> </o:p>
-Set 1: A4 (4)/B4(36) isidentical with A7/(4)B7(36); and <o:p></o:p>
<o:p> </o:p>
-Set 2: A11(10)/B11(42) isidentical with: A14(10/B14(42) is identical with A16(10)|B16 (42)
<o:p> </o:p>
3) Highlight cells that contain sets of duplicates withdifferent colours – for example:<o:p></o:p>
<o:p> </o:p>
Cell colour for Set1 (A4 /B4 & A7/B7) should be different to cell colour for Set 2 (A11/B11, A14/B14 & A16|B16). <o:p></o:p>
<o:p> </o:p>
This colouring should be dynamic – data in this table is updated on adaily basis so as the number of duplicates will vary all the time. <o:p></o:p>
<o:p> </o:p>
Thanks in advance <o:p></o:p>
<o:p> </o:p>
<o:p></o:p>

 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
gacicd said:
[This colouring should be dynamic – data in this table is updated on adaily basis so as the number of duplicates will vary all the time.
Click to expand...
I have assumed that these cells do not update by formulas but by a method that triggers the Worksheet_Change event.

Try this in a copy of your workbook.
To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & make some changes to the range A2:B21.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>  <SPAN style="color:#00007F">Dim</SPAN> a, b<br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> bDupe <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>  <br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A2:B21")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    Range("A2:B21").Interior.ColorIndex = xlNone<br>    a = Range("A1:B21").Value<br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(a, 1))<br>    k = 2<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> 20<br>      bDupe = <SPAN style="color:#00007F">False</SPAN><br>      <SPAN style="color:#00007F">If</SPAN> b(i) = "" <SPAN style="color:#00007F">Then</SPAN><br>        s = a(i, 1) & "|" & a(i, 2)<br>        <SPAN style="color:#00007F">For</SPAN> j = i + 1 <SPAN style="color:#00007F">To</SPAN> 21<br>          <SPAN style="color:#00007F">If</SPAN> a(j, 1) & "|" & a(j, 2) = s <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> bDupe <SPAN style="color:#00007F">Then</SPAN><br>              b(j) = k<br>            <SPAN style="color:#00007F">Else</SPAN><br>              bDupe = <SPAN style="color:#00007F">True</SPAN><br>              k = k + 1<br>              b(i) = k<br>              b(j) = k<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>          <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> j<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> 21<br>      <SPAN style="color:#00007F">If</SPAN> b(i) <> "" <SPAN style="color:#00007F">Then</SPAN><br>        Range("A" & i).Resize(, 2).Interior.ColorIndex = b(i)<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
Hi Peter <o:p></o:p>
Thank for your prompt response. <o:p></o:p>
Unfortunately cells (A2:B20) contain formulas and their valuesupdates when linked cells in other worksheets are updated. My apologies for notbeing specific about this bit. <o:p></o:p>
Is it possible to adjust your code to accommodate thiselement? <o:p></o:p>
Many thanks <o:p></o:p>
David<o:p></o:p>
 
Upvote 0
I'd put in two conditional formatting rules, one for each column. 'Highlight duplicates' is an option in the conditional formatting menu. Just make two, one for each range, and specify different formatting.
 
Upvote 0
Hi Merplap

Standard conditional formating highlights all sets of duplicates but with the same color.What I needed was something that can select different color for each set ofduplicates as outlined in my original thread. For example, Cell color for Set(A4 /B4 & A7/B7) should be different to cell color for Set 2 (A11/B11,A14/B14 & A16|B16)
 
Upvote 0
gacicd said:
Unfortunately cells (A2:B20) contain formulas and their valuesupdates when linked cells in other worksheets are updated.
Click to expand...
Are the other worksheets in the same workbook?

How many other sheets and ranges would affect the results in A2:B20 on this sheet?

What I'm driving at is that if there just one or two ranges on one or two sheets, then perhaps we could put some worksheet change code on those sheets to affect the results on our sheet of interest. To go down that path I would need to know
- the name of the sheet this A2:B20 range is on
- the ranges and their sheet names of all cells that are manually (or code) entered that end up affecting our A2:B20.


An alternative would be to use the Worksheet_Calculate event on our sheet of interest but that may result in this code running much more often than desired.
 
Upvote 0
Hi Peter<o:p></o:p>
Situation islike this:
Sheet of Interest is named: ”Table”.<o:p></o:p>
Range of Interest in “Table” is now D7:E26 (in my original correspondence it was A2:B21but I had to add some headings and explanations so the range has been alteredto accommodate these requirements – hope it would not cause you too manyproblems)<o:p></o:p>
Each pair of the data in the Range of Interest is obtainedfrom 20 different sheets labelled “A1” to “A20”. (allsheets are in the same workbook). Forexample <o:p></o:p>
<o:p> </o:p>
From <o:p></o:p>
 To<o:p></o:p>
Sheet “A1”/Cell F50<o:p></o:p>
 Sheet “Table”/ Cell D7<o:p></o:p>
Sheet “A1”/Cell F49<o:p></o:p>
 Sheet “Table”/ Cell E7<o:p></o:p>
Sheet “A2”/Cell F50<o:p></o:p>
 Sheet “Table”/ Cell D8<o:p></o:p>
Sheet “A2”/Cell F49<o:p></o:p>
 Sheet “Table”/ Cell E8<o:p></o:p>
:<o:p></o:p>
 :<o:p></o:p>
:<o:p></o:p>
 :<o:p></o:p>
Sheet “A20”/Cell F50<o:p></o:p>
 Sheet “Table”/ Cell D26<o:p></o:p>
Sheet “A20”/Cell F49<o:p></o:p>
 Sheet “Table”/ Cell E26<o:p></o:p>
<tbody> </tbody>
<o:p> </o:p>
All cells indata sheets (“A1”-“A20”) and Sheet of Interest ( “Table”) contain formulas. Forexample <o:p></o:p>
Location <o:p></o:p>
 Formula<o:p></o:p>
Sheet “A1”/Cell F50 <o:p></o:p>
 =I42<o:p></o:p>
Sheet “A1”/Cell F49 <o:p></o:p>
 =I19<o:p></o:p>
Sheet “Table”/ Cell D7<o:p></o:p>
 =IF('A1'!F50="","",'A1'!F50)<o:p></o:p>
Sheet “Table”/ Cell E7<o:p></o:p>
 =IF('A1'!F49="","",'A1'!F49)<o:p></o:p>
:<o:p></o:p>
 :<o:p></o:p>
:<o:p></o:p>
 :<o:p></o:p>
Sheet “A20”/Cell F50<o:p></o:p>
 =I42<o:p></o:p>
Sheet “A20”/Cell F49<o:p></o:p>
 =I19<o:p></o:p>
Sheet “Table”/ Cell D26<o:p></o:p>
 =IF('A20'!F50="","",'A20'!F50)<o:p></o:p>
Sheet “Table”/ Cell E26<o:p></o:p>
 =IF('A20'!F49="","",'A20'!F49)<o:p></o:p>
<tbody> </tbody>
<o:p> </o:p>
It seems tome that your second option (Worksheet_Calculateevent on the Sheet of Interest) would be more practical. <o:p></o:p>
In terms of code running muchmore often than desired is it possible to have a sub-option of the second option(e.g. a code that can be linked to a command button) so once all daily updatesare done to run the code and check duplicates. <o:p></o:p>
May thanks for all your work sofar. <o:p></o:p>
David <o:p></o:p>
 
Upvote 0
gacicd said:
.. a sub-option of the second option(e.g. a code that can be linked to a command button) so once all daily updatesare done to run the code and check duplicates.
Click to expand...
That seems like a sensible option to me.

The following macro would be placed in a standard Module & linked to your Command Button. (Don't forget to remove the Worksheet_Change code from the "Table" sheet's module if you haven't done that already.)

Code: 
Sub Check_Duplicates()
  Dim a, b
  Dim i As Long, j As Long, k As Long, rws As Long
  Dim s As String
  Dim bDupe As Boolean
  Dim DupeRange As Range
  
  Set DupeRange = Range("D7:E26") '<- Set this as desired
  
  With DupeRange
    .Interior.ColorIndex = xlNone
    a = .Value
    rws = UBound(a, 1)
    ReDim b(1 To rws)
    k = 2
    For i = 1 To rws - 1
      bDupe = False
      If b(i) = "" Then
        s = a(i, 1) & "|" & a(i, 2)
        For j = i + 1 To rws
          If a(j, 1) & "|" & a(j, 2) = s Then
            If bDupe Then
              b(j) = k
            Else
              bDupe = True
              k = k + 1
              b(i) = k
              b(j) = k
            End If
          End If
        Next j
      End If
    Next i
    For i = 1 To rws
      If b(i) <> "" Then
        .Cells(i, 1).Resize(, 2).Interior.ColorIndex = b(i)
      End If
    Next i
  End With
End Sub


If you did want to try the Worksheet_Calculate approach, also put the following code in the "Table" sheet's module.
It will most likely very quickly annoy you as the message box will pop up every time the sheet re-calculates and you will have to click OK.
I've just done that to try to let you assess how often it will actually happen.
If you are satisfied that is the way you want to go then just delete that message box line of code (and you wouldn't need the Command Button of course).

Code: 
Private Sub Worksheet_Calculate()
  MsgBox "Calculate triggered." & vbLf & "Duplicates about to be re-checked"
  Check_Duplicates
End Sub
 
Upvote 0
Peter <o:p></o:p>
You are genius <o:p></o:p>
Everything works well <o:p></o:p>
Thank you very much for your assistance. <o:p></o:p>
<o:p> </o:p>
Regards<o:p></o:p>
<o:p> </o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Unable to copy formula down sheet if data isn't there.
Replies
3
Views
145
Fluff
Fluff
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
480
Alex Blakenburg
A
Dexir
  • Solved
if multiple conditions
Replies
7
Views
480
Legacy 143009
L
C
  • Question
if Function
Replies
3
Views
246
ChetanPuri
C
F
  • Solved
Put a mark on rows that have the same 10 digits but different last one digit
Replies
2
Views
160
feni1388
F

Forum statistics

Threads
1,221,558
Messages
6,160,486
Members
451,651
Latest member
Penapensil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top