Highlight Duplicates within separate groups of data

Dave T · Oct 2, 2009

Hello All,

I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.

I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.

Can someone help me with some code which will make the fill red and when the duplicated has been corrected the fill colour is removed.

Thanking you in advance.

Regards,
Dave T

DUPLICATES - Many Macros.xls

A

B

C

D

29

PN3215

30

1

SOUTHERNAPPROACH

9107

31

3

NORTHERNAPPROACH

9105

32

2

GENERALUNDERSIDEOFWESTERNSTONEARCH

9111

33

1

WESTERNELEVATION

9104

34

3

GENERALUNDERSIDE

9110

35

36

PN7583

37

1

SOUTHERNAPPROACH

9111

38

2

NORTHERNAPPROACH

9112

39

1

GENERALUNDERSIDE

9113

Sheet 9

Dave T · Feb 26, 2010

Hello All,

I have another question regarding a modification/addition to Richard's code from reply number 8, and I have been trying to get this to work without much success...

I have inserted a new column between the Description (column B) and the Photo ID (was column C) that will contain the rest of the photo details.
I would like to just enter the photo number into column D and after pressing enter have this number copied to the cell to the left (column C) and have “IMG_” added before the number and “.JPG” added after the number.

For example if there was a photo number of 1234 added in column D, the cell immediately to the left would contain a description of IMG_1234.JPG.
I did try using a formula, but this affected the sorting macro, hence the need to modify the macro.

Thanks in advance,

Regards,
Dave T

Peter_SSs · Feb 26, 2010

Dave T said:
I have another question regarding a modification/addition to Richard's code from reply number 8, ...

Is that Richard also known as Peter?

Dave T said:
I have inserted a new column between the Description (column B) and the Photo ID (was column C) that will contain the rest of the photo details.
I would like to just enter the photo number into column D and after pressing enter have this number copied to the cell to the left (column C) and have “IMG_” added before the number and “.JPG” added after the number.

For example if there was a photo number of 1234 added in column D, the cell immediately to the left would contain a description of IMG_1234.JPG.
I did try using a formula, but this affected the sorting macro, hence the need to modify the macro.

It's a long time since I provided that code and I don't have that test sheet any more. However, I think this may do what you want. Make these changes to the code from post #8

1. Add a new Dim statement at the top:

Rich (BB code):

Dim ChangedD As Range

2. Change this line

Rich (BB code):

Set Changed = Intersect(Target, Range("A:D"))

3. This change may not be necessary but should do no harm

Rich (BB code):

Set DVRng = Intersect(Cells(r, 1).Resize(, 4).CurrentRegion, Columns("A"))

4. Add this new section just before 'Application.EnableEvents = True'

Rich (BB code):

Set ChangedD = Intersect(Changed, Columns("D"))
If Not ChangedD Is Nothing Then
    For Each c In ChangedD
        c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG"
    Next c
End If

Dave T · Feb 28, 2010

My appologies Peter,

I had just responded to a private message from Richard Schollar and still had his name on my brain.

Your reply was very much appreciated and by coincidence I had done virtually all of what you had suggested, i.e. I had done steps 1, 2 & 3 exactly as you had suggested and had step 4 most of the way there.
The line

Code:

 [COLOR=#0000ff]c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG" [/COLOR][COLOR=black]

was the part that had me stumped.[/COLOR]

When I enter the code above I get a message that says "Compile error: Syntax error.". I even tried to play around with your code

Code:

 [COLOR=#0000ff]c.Value = c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG" [/COLOR][COLOR=black]

to no avail.[/COLOR]
What am I doing wrong???

Sorry again about confusion with your name.

Regards,
Dave T

Peter_SSs · Feb 28, 2010

I tested the code successfully (no syntax error) so I am not sure what the problem is with your code. Here's my full code:

 Private Sub Worksheet_Change(ByVal Target As Range) Dim Changed As Range, ChangedB As Range, c As Range, DVRng As Range Dim ChangedD As Range Dim r As Long Set Changed = Intersect(Target, Range("A:D")) If Not Changed Is Nothing Then Application.EnableEvents = False r = Changed.Row Set DVRng = Intersect(Cells(r, 1).Resize(, 4).CurrentRegion, Columns("A")) With DVRng.Validation .Delete .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="=COUNTIF(" & DVRng.Address(1, 1) _ & "," & DVRng.Cells(1, 1).Address(0, 0) & ")=1" .IgnoreBlank = True .ErrorMessage = "Duplicates not allowed" .ShowError = True End With Set ChangedB = Intersect(Changed, Columns("B")) If Not ChangedB Is Nothing Then For Each c In ChangedB c.Value = UCase(c.Value) Next c End If Set ChangedD = Intersect(Changed, Columns("D")) If Not ChangedD Is Nothing Then For Each c In ChangedD c.Offset(, -1).Value = "IMG_" & c.Value & ".JPG" Next c End If Application.EnableEvents = True End If End Sub

Dave T · Feb 28, 2010

Hello Peter,

Yes you are right your code does work...
Not sure why I had that error message.
At home I was using Excel 2007 (which is where I had the error message) and here at work I am using Excel 2003 and there are no problems.

Once again thanks for all your help, it is very much appreciated.

Regards,
Dave T

Dave T · May 27, 2010

Hello All,

I have a question about whether it is possible to add other conditions to the data validation VBA used in column A that prevents duplicates within separate groups of data.

The prevention of duplicates VBA provided by Peter works perfectly, but I am wondering if it is possible to add another two conditions to the existing criteria…

Extra Data Validation criteria:
1) Only allow ‘Whole numbers’
2) Numbers within each separate group of data must be within sequential order

Option number 2 may not be possible to do as the sorting order whole numbers are entered in a random order in column A against the photo descriptions in column B. The only time they will/should be in numerical order is when the sort order macro is run. This sorts the data within the specific group in columns A, B & C by column A.
I am hoping to prevent users from not using a non numerical sequence i.e. they could enter 1, 2, 3, 4 in random order but not be able to enter 1, 2, 3, 5 (they have missed number 4).

If the sequential number option is too hard I may have to settle for using conditional formatting that will colour cells that are not in sequential order (however this would only be evident when the data has been sorted). If others agree this looks like the easiest option can someone suggest a conditional formatting formula that will highlight numbers not in sequential order, but only apply within the various ranges of data.

Thanking you in advance.

Regards,
Dave T

Peter_SSs · May 29, 2010

Once again quite a deal of time has passed since doing this so I'm not sure if I remember this exactly, but see how these blue changes go:

Rich (BB code):

With DVRng.Validation
    .Delete
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=AND(COUNTIF(" & DVRng.Address(1, 1) _
        & "," & DVRng.Cells(1, 1).Address(0, 0) & ")=1,MOD(" & DVRng.Cells(1, 1).Address(0, 0) & ",1)=0," _
        & DVRng.Cells(1, 1).Address(0, 0) & ">=1," _
        & DVRng.Cells(1, 1).Address(0, 0) & "<=ROWS(" & DVRng.Address(1, 1) & ")-1)"
    .IgnoreBlank = True
    .ErrorMessage = "You probably want a different message here now"
    .ShowError = True
End With

Dave T · May 31, 2010

Hello Peter,

You blow me away with you knowledge...
You solution works perfectly. Thank you very much for all of your help.

I used the macro recorder to try to work this out and the output did not look anything like what you produced.

I just have two more things I have spent hours trying to find/work out.
1) Stand alone VBA code that I can call from another macro (most likely “Sort_A_to_C”) that will put a border around the region i.e. if the various separate ranges of data have photo descriptions in column B and the associated photo ID numbers in column C. The macro will put external and internal borders around the A to C range on a range by range basis for however many rows are used but not including the conditionally formatted bold “PN…” header. The data in columns B or C will define the rectangular A to C range.
I have tried using the “With ActiveCell.CurrentRegion” with other code I have found on the web and from the vba recorder without much success.
2) Conditional formatting for column A that will highlight where numbers have been missed.
• If a row is blank nothing is conditional formatted
• If the cell is the blank to the left on the PN heading nothing is conditional formatted
• If the numbers are in sequential order nothing is conditional formatted
• If a number has been missed it will be conditional formatted i.e. 1, 2, 3, 5, 6, 9, 10. Cells 5 & 9 would be highlighted to show they are not in numerical order (the next numbers have been jumped).
I have tried =IF(AND($A43<>"",$A42+1=$A43,LEFT($B43,2)<>"PN"),FALSE,TRUE) and many other combinations without much success.

Once again any help would be appreciated.

Regards,
Dave T

Peter_SSs · May 31, 2010

1. Try this (only tested in Excel 2007):

 Sub DavesBorders() Dim cRng As Range, cArea As Range Application.ScreenUpdating = False Set cRng = Range("C2", Range("C" & Rows.Count).End(xlUp)) cRng.Offset(, -2).Resize(, 3).Borders.LineStyle = xlNone For Each cArea In cRng.SpecialCells(xlCellTypeConstants).Areas With cArea.Offset(, -2).Resize(, 3) .BorderAround LineStyle:=xlContinuous, Weight:=xlThin, ColorIndex:=0 With .Borders(xlInsideVertical) .LineStyle = xlContinuous .ColorIndex = 0 .Weight = xlThin End With With .Borders(xlInsideHorizontal) .LineStyle = xlContinuous .ColorIndex = 0 .Weight = xlThin End With End With Next cArea Application.ScreenUpdating = True End Sub 

2. Try this 'Formula is' in Conditional Formatting for, say, row 43:

=AND($A43<>"",$A43<>$A42+1)

Dave T · Jun 8, 2010

Thank you very much once again for all your help Peter,

All you suggestions work fine.

I was so close to the Conditional Formatting formula, but was getting frustrated and seemed to be going around in circles. Your solution works perfectly.

Your solution to the borders was an interesting approach, even though it operates differently than what I was thinking or expecting.
With your solution I could enter all the data in their various range groups and when the borders macro is called from the sort macro it puts borders around all the various groups of data on the entire worksheet.
I was thinking/trying to get the border macro just to put borders around the specific range that was being sorted.

Just as an aside… I have noticed that there is a problem when you run your border macro which puts a border around all data in columns A to C, not including the PN** heading. If you do this and move onto another range of data, the data validation kicks in and prevents duplicates being entered.
As the border macro uses .End(xlUp) does this confuse the validation macro into thinking all separated ranges of data is now one large range ???

Once again thanks for all your help.

Regards,
Dave T

Highlight Duplicates within separate groups of data

Dave T

Board Regular

Dave T

Board Regular

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Dave T

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Dave T

Board Regular

Dave T

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Dave T

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Dave T

Board Regular

Similar threads

Forum statistics

Share this page

Highlight Duplicates within separate groups of data

Board Regular

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock