Finding duplicate values
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Finding duplicate values
Good morning....I need some help with a formula that will detect duplicate values within similar sets of data. The attached workbook should illustrate the question: I want to check each row and find the duplicates within a row (which will return "1" in column N) and will trigger C/Formatting (white on red) on a date in column C if a duplicate exists for that day. A "duplicate" is considered to be any value that is 'duplicated' (obviously) as well as any value that is preceded by asterisks, or followed by an AM or PM (eg: 123 and *123 or **123 would be considered duplicates; Car and Car - P.M. are considered duplicates....I guess the formula will have to 'overlook' the asterisks and the AM or PM suffix..??..??.
I have entered 2 different formula in N4 and N7, trying to use one of these as a means of checking for duplicates (and returning a "1" in col N)........any suggestions? Thanks in advance
I have entered 2 different formula in N4 and N7, trying to use one of these as a means of checking for duplicates (and returning a "1" in col N)........any suggestions? Thanks in advance
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
Would "Car - A.M." and "Car - P.M." be considered duplicates?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Finding duplicate values
If you select the D4:M25 range in your example and use the conditional formatting formula:
=SUM(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D4:$M4," - A.M.","")," - P.M.",""),"*",""))=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4," - A.M.","")," - P.M.",""),"*",""),1))>1
You can set a color to highlight the duplicates.
If you don't want to highlight duplicates null cells you can use:
=AND(D4<>"",SUM(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D4:$M4," - A.M.","")," - P.M.",""),"*",""))=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4," - A.M.","")," - P.M.",""),"*",""),1))>1)
Steve
=SUM(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D4:$M4," - A.M.","")," - P.M.",""),"*",""))=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4," - A.M.","")," - P.M.",""),"*",""),1))>1
You can set a color to highlight the duplicates.
If you don't want to highlight duplicates null cells you can use:
=AND(D4<>"",SUM(IF((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D4:$M4," - A.M.","")," - P.M.",""),"*",""))=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4," - A.M.","")," - P.M.",""),"*",""),1))>1)
Steve
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Good morning...Hans -- Car AM and Car PM would not be duplicates...I suppose I should have given a bit more context to my sample workbook.....the names along the top row (row 2) are people and the data that is entered in D3:M25 are places...these are rooms or offices and I want to know if I have accidentally put more than 1 person in the same spot (hence, the number 1 in col N and the red alert in the date column....the asterisks serve another purpose and so I need to know, for example, if I have double-scheduled people into the 'location' known as 123, or the 'location' known as Car - A.M.......and sometimes, a site will be identified as ?123 or ?House and the ? should not serve to differentiate the 2 locations (eg: house and ?house are one and the same for these purposes)......hope this helps.
Steve - I tried both of your suggestions and both returned #VALUE ..??..??
Steve - I tried both of your suggestions and both returned #VALUE ..??..??
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Yes...2 occurrences of the same thing on the same row is a dup....so, 'Home' and 'Home' is a dup.....'Home' and 'Home - A.M.' is a dup...'Home' and 'Home - P.M.' is a dup....'Home' and '*Home' are dups...'Home - A.M.' and 'Home - P.M.' are not duplicates........the duplicates can be permitted to co-exist, I just want to have a red alert that they do exist (ie that I have scheduled more than 1 person in the same room at the same time) and then I can make a change or ignore it.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
Formulas would be complicated. Here is a macro that will highlight the duplicate values and populate column N. Modify the constants at the beginning as needed.
Code: Select all
Sub FindDups()
Const FirstRow = 3
Const DateCol = 3 ' C
Const FirstCol = 4 ' D
Const LastCol = 13 ' M
Const NoteCol = 14 ' N
Dim LastRow As Long
Dim CurRow As Long
Dim CurCol As Long
Dim NxtCol As Long
Dim CurVal As String
Dim NxtVal As String
LastRow = Cells(Rows.Count, DateCol).End(xlUp).Row
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Interior.ColorIndex = xlColorIndexNone
Range(Cells(FirstRow, NoteCol), Cells(LastRow, NoteCol)).ClearContents
For CurRow = FirstRow To LastRow
For CurCol = FirstCol To LastCol - 1
CurVal = LCase(Replace(Cells(CurRow, CurCol).Value, "*", ""))
If CurVal <> "" Then
For NxtCol = CurCol + 1 To LastCol
NxtVal = LCase(Replace(Cells(CurRow, NxtCol).Value, "*", ""))
If CurVal = NxtVal Or _
CurVal = Replace(NxtVal, " - A.M.", "") Or _
CurVal = Replace(NxtVal, " - P.M.", "") Or _
NxtVal = Replace(CurVal, " - A.M.", "") Or _
NxtVal = Replace(CurVal, " - P.M.", "") Then
Cells(CurRow, CurCol).Interior.Color = vbYellow
Cells(CurRow, NxtCol).Interior.Color = vbYellow
Cells(CurRow, NoteCol).Value = 1
End If
Next NxtCol
End If
Next CurCol
Next CurRow
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Thank you for all of this.....I will try this macro, as well as the formulas that Steve provided...I think that his are supposed to be array formula (CTRL+SHIFT+ENTER..?) and I will let you know how I make out...thank you both/
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Finding duplicate values
My formulas are for entering into conditional formatting to color the duplicate cells (which I thought was the question). In conditional formatting (as I gave) the control-shift-enter does not need to be used. My technique will not work if "Car - A.M" <> "Car - P.M." My formula will convert them both to "Car" and to distinguish them will be overly complex with formulas, a UDF is probably required...
Steve
Steve
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Good morning, Hans and Steve....I thought that Steve's formula was working...I will try to clarify what I am trying to do, espec b/c it seems it was somewhat unclear to Steve. I have attached a new sample.....the worksheet is set up to schedule people into places. The places are designated by numbers or names. The * or the ? etc are added to a place in order to trigger c/formatting relative to that place (eg: ***123 turns a cell yellow, in my real workbook, and that yellow cell means that it is an unfinished job at that site....3 asterisks + rome (***rome) goes blue and means that the job at rome is a multi-day assignment...etc etc...I am trying to set up something that will color cells white-on-red in the date column if I schedule more than 1 person to the same job site (b/c that is a 'conflict' in the schedukling)..in the attached workbook, I have set up a more realistic schedule and have indicated whether there is a conflict, and what causes the conflict.....in essence, if a job site shows up more than once within a row, then that is a conflict and I am alerted to it by a color change in column C......and then I either clear the conflict or allow it to remain
...the only exception would be 'job site - a.m.' and 'job site - p.m.' or '123 - a.m.' and '123 - p.m.' (b/c one person can be at the location in the morning, and leave, and a second person arrive for the afternoon)......and so I am looking for something that will return TRUE or 1 in column N and cause the date in column C to change colors.....is this a better explanation?
...the only exception would be 'job site - a.m.' and 'job site - p.m.' or '123 - a.m.' and '123 - p.m.' (b/c one person can be at the location in the morning, and leave, and a second person arrive for the afternoon)......and so I am looking for something that will return TRUE or 1 in column N and cause the date in column C to change colors.....is this a better explanation?
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
Good evening.
See the attached version (the macro belongs in a standard module, not in the worksheet module).
See the attached version (the macro belongs in a standard module, not in the worksheet module).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Hi Hans.....thank you for this...I dloaded it, and added a couple more 'qualifiers'...I now have the *, the ? and a ^...all seems to be good, and I am going to insert it into my real workbook and adjust the start columns, rows etc.....hopefully, that won't present any problems...I have 2 questions: using the sample that you posted, can there be some intervening columns b/w M and N? Next question is: I can't remember how to access 'commands' for the VBA and rather than change the cell colors yellow to highlight the conflicting cells, I want to change the font in the cell to BOLD....I suspect that I should remove "Interior.Color = vbYellow" and use "Font = Bold", but I can't remember how to get FONT into the mix.....where do I find it? Thanks
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
You can change the constants at the beginning of the macro to match the 'real' situation:
To make the font bold:
Code: Select all
Const FirstRow = 3 - first row with place codes
Const DateCol = 3 ' C - column with dates
Const FirstCol = 4 ' D - first column with place codes
Const LastCol = 13 ' M - last column with place codes
Const NoteCol = 14 ' N - column to fill with 1 for duplicates; does not have to be adjacent to LastCol
Code: Select all
Cells(CurRow, CurCol).Font.Bold = True
Cells(CurRow, NxtCol).Font.Bold = True
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
I forgot: you'll have to change the line
to
to reset the Bold property of the cells.
Code: Select all
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Interior.ColorIndex = xlColorIndexNone
Code: Select all
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Font.Bold = False
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
Thank you...not sure that this will work, unfortunately -- I have it entered in my workbook in a standard module. I also have a worksheet module (Worksheet Change Event that sets page breaks - you gave it to me) on that same page and when the 'FindDups' macro runs, it is for F15:BC318 (a big area) and it seems to me conflicting with the worksheet module....the code gets interrupted and I get flipped over to the worksheet module with a yellow highlight on the first line that the worksheet module.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
That can be prevented easily:
Code: Select all
Sub FindDups()
Const FirstRow = 3
Const DateCol = 3 ' C
Const FirstCol = 4 ' D
Const LastCol = 13 ' M
Const NoteCol = 14 ' N
Dim LastRow As Long
Dim CurRow As Long
Dim CurCol As Long
Dim NxtCol As Long
Dim CurVal As String
Dim NxtVal As String
' Prevent Worksheet_Change event from running
Application.EnableEvents = False
LastRow = Cells(Rows.Count, DateCol).End(xlUp).Row
' Reset Bold
Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Font.Bold = False
' Clear 1s
Range(Cells(FirstRow, NoteCol), Cells(LastRow, NoteCol)).ClearContents
' Loop
For CurRow = FirstRow To LastRow
For CurCol = FirstCol To LastCol - 1
' Get cell value and strip it
CurVal = Replace(Cells(CurRow, CurCol).Value, "?", "")
CurVal = Replace(CurVal, "^", "")
CurVal = LCase(Replace(CurVal, "*", ""))
If CurVal <> "" Then
' Loop through cells to the right
For NxtCol = CurCol + 1 To LastCol
' Get value and strip it
NxtVal = Replace(Cells(CurRow, NxtCol).Value, "?", "")
NxtVal = Replace(NxtVal, "^", "")
NxtVal = LCase(Replace(NxtVal, "*", ""))
' Compare values
If CurVal = NxtVal Or _
CurVal = Replace(NxtVal, " - a.m.", "") Or _
CurVal = Replace(NxtVal, " - p.m.", "") Or _
NxtVal = Replace(CurVal, " - a.m.", "") Or _
NxtVal = Replace(CurVal, " - p.m.", "") Then
' We have a duplicate - make cells bold
Cells(CurRow, CurCol).Font.Bold = True
Cells(CurRow, NxtCol).Font.Bold = True
' And enter a 1 in the appropriate column
Cells(CurRow, NoteCol).Value = 1
End If
Next NxtCol
End If
Next CurCol
Next CurRow
' Enable Worksheet_Change again
Application.EnableEvents = True
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
But the problem is this: earlier, I said that the * and the ? and the ^ are used to trigger c/formattting (eg: *123 turns the cell background yellow. **123 turns it blue, ***4546 makes it rose, ?home makes it light brown etc etc)...other C/F on the worksheet is things like "If cell value = Conference", the the c/f is white-text-on-blue-background.........the FindDups code, when it runs, seems to wipe out that color coding even if it isn't a cell that contains a duplicate entry
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
I don't see why it should do that - see the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 200
- Joined: 24 Aug 2011, 13:13
Re: Finding duplicate values
It was getting hung up on the first "End If" and now it doesnt even get that far -- It gets hung up on the line that says: Cells(CurRow, NxtCol).Font.Bold = True
I am guessing that this code runs over the entire worksheet...the range that is involved is F15:BC381......can you confine it to that range so that it runs faster? And, when there is a statutory holiday, all cells in that row are shown as Closed...is there a way to have this code 'overlook' the word 'Closed' and not apply itself to multiple instances of 'Closed" ?
I am guessing that this code runs over the entire worksheet...the range that is involved is F15:BC381......can you confine it to that range so that it runs faster? And, when there is a statutory holiday, all cells in that row are shown as Closed...is there a way to have this code 'overlook' the word 'Closed' and not apply itself to multiple instances of 'Closed" ?
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Finding duplicate values
The code runs on the range specified by FirstRow, LastRow, FirstCol and LastCol.
The attached version excludes "Closed" from the search for duplicates.
The attached version excludes "Closed" from the search for duplicates.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans