Need guidance on finding duplicate data in a multisheet workbook

[gray_b]

I have a 25 sheet workbook, that I need to highlight duplicates in only 1 column (the same column) in each sheet. Each sheet has another 15 columns of other data. The data is a 4 digit number. But maybe changing that to a string of text in the future.

I have tried using conditional formatting, but this is not reliable over multi sheets. It works in a limited way, but it does not pick up all duplicates.

I have tried 'record macro' in createing conditional formating, but it does not record anything. Was hoping on using the code, and inserting it into each worksheet.

I have scoured google, and I have tried numerous vba code snippets. But again nothing fits my requirements.

I need guidance on creating a suitable macro that uses "Private Sub Workbook_Open()"

Any advice and guidance please.

 

[DanteAmor]

Review this:

https://www.youtube.com/watch?v=0BXka20-nOk


An example to compare sheet1 with sheet2 and sheet3

=AND(B2<>"",OR(COUNTIF(nsheet2,B2)>0,COUNTIF(nsheet3,B2)>0))


Note: nsheet2 and nsheet3 are range names in column B of your respective sheet

 

[DanteAmor]

Or you can put in an auxiliary column the sum of counts, if greater than 0 then there is a duplicate. In your conditional format you can refer to the auxiliary column.

Formula in auxiliary column

=COUNTIF(nsheet2,B3)+COUNTIF(nsheet3,B3)+COUNTIF(nsheet4,B3)

 

[gray_b]

Review this:

https://www.youtube.com/watch?v=0BXka20-nOk


An example to compare sheet1 with sheet2 and sheet3

=AND(B2<>"",OR(COUNTIF(nsheet2,B2)>0,COUNTIF(nsheet3,B2)>0))


Note: nsheet2 and nsheet3 are range names in column B of your respective sheet

Sorry DanteAmor, I cannot get that to work at all.

 

[DanteAmor]

Try my test file

https://www.dropbox.com/s/dzo163a1r5huhzo/duplicate data in a multisheet.xlsx?dl=0

 

[gray_b]

Try my test file

https://www.dropbox.com/s/dzo163a1r5huhzo/duplicate data in a multisheet.xlsx?dl=0

Really strange.

I copied the condition formatting over from your workbook, to my workbook and nothing works.

However in saving your workbook and coping my data over to your workbook, it works.

Must be a bug somewhere in my Excel 2019? or in the Excel template.

Anyhow.

It does not highlight duplicates that are in the 1st worksheet.
Or the duplicates in 2nd worksheet that occur in the 1st worksheet.
Or any duplicates between Worksheet 2 and Worksheet 3.

It only compares worksheet1 to worksheet2 and then 1 to 3


Anyhow good going, any further input to get me on the right track.

 

[DanteAmor]

Of course, it's just an example so you can take it as a reference and make all the references you need.

Any advice and guidance please.

You could try the following code in the open event, check all the values ​​in column B of all the sheets against column B of all the sheets.

Private Sub Workbook_Open()    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    c = "B"
    
    For Each sh In Sheets
        sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
    Next
    '
    For Each sh In Sheets
        For Each d In sh.Range(c & "1", sh.Range(c & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
            If d.Interior.ColorIndex = xlNone Then
                For Each sh2 In Sheets
                    If sh2.Name <> sh.Name Then
                        Set b = sh2.Range(c & ":" & c).Find(d.Value, LookIn:=xlValues, lookat:=xlWhole)
                        If Not b Is Nothing Then
                            d.Interior.ColorIndex = 6
                            b.Interior.ColorIndex = 6
                        End If
                    End If
                Next
            End If
        Next
    Next
End Sub

Try and tell me.

 

[gray_b]

Of course, it's just an example so you can take it as a reference and make all the references you need.



You could try the following code in the open event, check all the values ​​in column B of all the sheets against column B of all the sheets.

Private Sub Workbook_Open()    Dim sh As Worksheet, sh2 As Worksheet, c As String, d As Range, b As Range
    c = "B"
    
    For Each sh In Sheets
        sh.Range(c & ":" & c).Interior.ColorIndex = xlNone
    Next
    '
    For Each sh In Sheets
        For Each d In sh.Range(c & "1", sh.Range(c & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants, 23)
            If d.Interior.ColorIndex = xlNone Then
                For Each sh2 In Sheets
                    If sh2.Name <> sh.Name Then
                        Set b = sh2.Range(c & ":" & c).Find(d.Value, LookIn:=xlValues, lookat:=xlWhole)
                        If Not b Is Nothing Then
                            d.Interior.ColorIndex = 6
                            b.Interior.ColorIndex = 6
                        End If
                    End If
                Next
            End If
        Next
    Next
End Sub

Try and tell me.

That works spot on, first time I tried it. Now I will try and incorporate it into my actual workbook.

 

[DanteAmor]

Let me know if you have any doubt.

 

[gray_b]

How can I get it, so it does use the first worksheet.