Finding duplicate values

[dmcnab]

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

[HansV]

Would "Car - A.M." and "Car - P.M." be considered duplicates?

[sdckapr]

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

[dmcnab]

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 ..??..??

[HansV]

Would 2 occurrences of "Home" count as duplicates?

[dmcnab]

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.

[HansV]

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

[dmcnab]

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/

[sdckapr]

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

[dmcnab]

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?

[HansV]

Good evening.

See the attached version (the macro belongs in a standard module, not in the worksheet module).

Finding duplicates 2.xlsm

[dmcnab]

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

[HansV]

You can change the constants at the beginning of the macro to match the 'real' situation:

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

To make the font bold:

Code: Select all

                        Cells(CurRow, CurCol).Font.Bold = True
                        Cells(CurRow, NxtCol).Font.Bold = True

[HansV]

I forgot: you'll have to change the line

Code: Select all

    Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Interior.ColorIndex = xlColorIndexNone

to

Code: Select all

    Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Font.Bold = False

to reset the Bold property of the cells.

[dmcnab]

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.....

[HansV]

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

[dmcnab]

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

[HansV]

I don't see why it should do that - see the attached version.

Finding duplicates 2.xlsm

[dmcnab]

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" ?

[HansV]

The code runs on the range specified by FirstRow, LastRow, FirstCol and LastCol.

The attached version excludes "Closed" from the search for duplicates.

Finding duplicates 2.xlsm