Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Finding Duplicates across multiple worksheets

0
Hi.  I am new to this forum.   I would like to have a macro that looks into multiple worksheets in Excel and identify duplicates in column A by highlighting them.  These worksheets are many and have various tab names.   When running the macro I need it to highlight each duplicate on all the worksheets in column A so that if there is a duplicate on one worksheet and it is also on other worksheets they also  would be highlighted.   I have looked up a lot of stuff online and I can't find anything close to what I need.    Thanks.
Answer
Discuss

Discussion

No, you won't find anything like that because your data aren't good, being spread over several worksheets. Plan on combining them into one worksheet first, then doing the sorting and marking in the way you find others have done before you. If you need the entries to be returned to their original worksheets thereafter, you might add a column with the sheet's name when joining the data so that you can sort them back.
Variatus (rep: 4889) Apr 24, '17 at 5:51 am
Add to Discussion

Answers

0
Selected Answer

I decided to see if I could make a macro to do this in 6 minutes or less and I think I got the perfect solution for you.

Sub worksheet_duplicates_highlight()

'count the number of worksheets in the workbook
sheet_count = ActiveWorkbook.Worksheets.Count

'loop through the worksheets in the workbook
For a = 1 To sheet_count

    'get the number of rows
    row_count_a = Sheets(a).Range("A" & Rows.Count).End(xlUp).Row

    'loop through the rows in the worksheet
    For b = 1 To row_count_a

        'get the value for which a duplicate is being searched
        original_value = Sheets(a).Cells(b, 1).Value

        'loop through the worksheets looking for a duplicate
        For c = 1 To sheet_count

            'get the number of rows
            row_count_c = Sheets(c).Range("A" & Rows.Count).End(xlUp).Row

            'loop through the rows in each sheet
            For d = 1 To row_count_c

                'get the value that is being checked for
                check_value = Sheets(c).Cells(d, 1).Value

                'make sure this is not the original cell
                If a <> c And b <> d Then

                    If original_value = check_value Then

                        Sheets(c).Cells(d, 1).Interior.Color = RGB(255, 0, 0)

                    End If

                End If

            Next d

        Next c

    Next b

Next a

End Sub

I included comments in the code as well.

Note that this might take a while in a large spreadsheet and you might want to turn off screen-updating.

Discuss

Discussion

Nice!
gebobs (rep: 30) Apr 26, '17 at 9:33 am
Thanks! I will try it out when I get to work!!! 
LymaBeane (rep: 2) Apr 26, '17 at 11:48 am
It works perfectly!  Thanks so much!  And you did it in 6 minutes!  Wow!!!
Again, thanks!
LymaBeane (rep: 2) Apr 26, '17 at 9:22 pm
If this answer works better then you should de-select the other one and select this one. (sorry gebobs)
don (rep: 1989) Apr 27, '17 at 5:21 am
Add to Discussion
0

No macro needed. A formula will do the trick. Something like this will count the number of records in Sheet2 that match a certain record in Sheet1. 

=COUNTIF(Sheet2!A:A,A1)>0

The formula can be modified to include all sheets.

Discuss

Discussion

There are over 40 worksheets. How would I modify the formula? 
LymaBeane (rep: 2) Apr 25, '17 at 9:13 am
It would be complicated but there is more than one way to skin that cat. For simplicity, let's say the sheets are named S1, S2, S3, ..., S50. You could have a formula in S1 like:

=IF(COUNTIF(S2!A:A,A1)>0," S2","") & IF(COUNTIF(S3!A:A,A1)>0," S3","") & ... & IF(COUNTIF(S50!A:A,A1)>0," S50","")

It'll be a long formula, but you have up to 8192 characters. And if you want to do the same for the other sheets, then you'll have to copy and modify it accordingly. So for S2, it would be the same formula but that first term would change...


=IF(COUNTIF(S1!A:A,A1)>0," S1","") & IF(COUNTIF(S3!A:A,A1)>0," S3","") & ... & IF(COUNTIF(S50!A:A,A1)>0," S50","")
gebobs (rep: 30) Apr 25, '17 at 9:29 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login