Conditional format formula to look for duplicates in multiple columns

[sonosite]

I am using excel for making out a shift schedule for my company. How do I create a conditional format formula to look for duplicate initials in multiple columns and to repeat the assessment independently for many more rows?

Additionally, the formula would only look at the first 2 characters in a cell to check for duplication.


Example of data


Site1 Site2 Site3 Site4 Site5
1/1/2015 AA BB CC DD EE
1/2/2015 BB CC BB AA EE
1/3/2015 CC CC CC BB AA
1/4/2015 BB CC BB-t AA EE


First row has unique initials of AA to EE. There is no error with this row.
Second row has duplicated BB in two columns that I want the formula to highlight.
Third row has duplicated CC in three columns that I want the formula to highlight.
Fourth row has duplicated BB in two columns when you look at the first two characters in the cell that I want the formula to highlight.


Since this is a day by day basis, creating a conditional formating for each row individually would be unrealistic. The formula needs to look at every single row independently in the spreadsheet.

Thanks.

 

[sonosite]

Again, thank you! This will help me complete the shift schedule without duplication.

Another question. After I finish the shift schedules within excel, I have to start "data mining" the spreadsheet. I am not sure of the best approach and wanted to get your expert opinion of the best strategy. Is there a way to extract data based on the initials (first 2 characters in a cell only).

So for example, here is an exampled finished schedule

A	B	C	D	E
	Site1	Site2	Site3
1/1/2015	AA	BB	CC
1/2/2015	BB	AA	CC
1/3/2015	AA	CC	BB
1/4/2015	DD-r	BB	AA
1/5/2015	DD	AA	BB-t

<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 62px;"><col style="width: 56px;"><col style="width: 56px;"><col style="width: 56px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

</tbody>

I would like to generate a new worksheet/spreadsheet that summarizes something as follows:

AA	BB	CC	DD
1/1/2015, Site1	1/1/2015, Site2	1/1/2015, Site3	1/4/2015, Site1
1/2/2015, Site2	1/2/2015, Site1	1/2/2015, Site3	1/5/2015, Site1
1/3/2015, Site1	1/3/2015, Site3	1/3/2015, Site2
1/4/2015, Site3	1/4/2015, Site2
1/5/2015, Site2	1/5/2015, Site3

<colgroup><col style="width: 49px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="bgcolor: #b7b7b7"][/TD]
[TD="bgcolor: #b7b7b7"]A[/TD]
[TD="bgcolor: #b7b7b7"]B[/TD]
[TD="bgcolor: #b7b7b7"]C[/TD]
[TD="bgcolor: #b7b7b7"]D[/TD]

[TD="bgcolor: #b7b7b7, align: right"]1[/TD]

[TD="bgcolor: #b7b7b7, align: right"]2[/TD]

[TD="bgcolor: #b7b7b7, align: right"]3[/TD]

[TD="bgcolor: #b7b7b7, align: right"]4[/TD]

[TD="bgcolor: #b7b7b7, align: right"]5[/TD]

[TD="bgcolor: #b7b7b7, align: right"]6[/TD]

</tbody>

 

[Marcelo Branco]

Maybe this macro

Assumes
Data in Sheet1
Arranged data in Sheet2

Sub aTest()
    Dim dict As Object
    Dim lastRow As Long, lastCol As Long, rngData As Variant
    Dim i As Long, j As Long
    Dim spl As Variant, v As Variant, col As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    With Sheets("Sheet1") '<--Data sheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        rngData = Range(.Cells(1, 1), .Cells(lastRow, lastCol)).Value
    End With
        
    For i = 2 To UBound(rngData, 1)
        For j = 2 To UBound(rngData, 2)
            dict.Item(Left(rngData(i, j), 2)) = _
            dict.Item(Left(rngData(i, j), 2)) & rngData(i, 1) & ", " & rngData(1, j) & ";"
        Next j
    Next i
      
    With Sheets("Sheet2") '<--Destination sheet
        .Range("A1").Resize(, dict.Count).Value = dict.keys
        For Each v In dict.keys
            col = col + 1
            spl = Split(dict(v), ";")
            .Cells(2, col).Resize(UBound(spl, 1) + 1).Value = Application.Transpose(spl)
        Next v
        
        .Columns(1).Resize(, dict.Count).AutoFit
    End With
    
End Sub

M.